title: Invalid character value for cast specification (#0) - I know the problem, but not how to resolve it
body: <p>I'm having a problem with a button in MS Access querying a SQL View that doesn't have a foreign key.</p>
  
  <p>My question is VERY similar to the question found here: <a href="https://stackoverflow.com/questions/1522930/ms-access-error-odbc-call-failed-invalid-character-value-for-cast-specificatio">MS Access error &quot;ODBC--call failed. Invalid character value for cast specification (#0)&quot;</a> -- That seems to be my exact problem, but I'm not sure how to resolve it. Here are some more details. First, the exact error message:</p>
  
  <pre><code>ODBC--call failed.
  [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (#0)
  </code></pre>
  
  <p>Here's where I differ. I have a button in MS Access that does the following:</p>
  
  <pre><code>Private Sub btnMachineCutSheet_Click()
      Dim stDocName As String
  
      stDocName = "qryCutSheetByMachines"
      DoCmd.OpenQuery stDocName, acNormal, acEdit
  
  End Sub
  </code></pre>
  
  <p>The query you see listed there (<em>qryCutSheetByMachines</em>) is a pretty long query, but I've narrowed the problem down to one line. Here's the query:</p>
  
  <pre><code>SELECT 
    vwCutSheet.Network, 
    vwCutSheet.NetworkSpeed, 
    vwCutSheet.Duplex
  FROM vwCutSheet 
  INNER JOIN local_tblCreateCutSheet 
    ON vwCutSheet.EquipmentID = local_tblCreateCutSheet.EquipmentID;
  </code></pre>
  
  <p><em>Keep in mind that all the tables/views are linked tables from a MS SQL 2008 database</em></p>
  
  <p>The first bit of oddness is that everything works just fine if I remove the <em>vwCutSheet.NetworkSpeed</em>, line. Unfortunately, I need that data.</p>
  
  <p>Now, if I take out the JOIN statement the query works fine. Obviously I need the join or I wouldn't have it there. Now, the problem (I assume) is that the view (vwCutSheet) does not have a PK (<em>should views have primary keys?</em>). vwCutSheet.EquipmentID cannot be a PK though because there will usually always be a case of multiple EquipmentID's with the same value in this view.</p>
  
  <p>And the last bit of information that might be messing things up is this. If I open up the view in Design view (in MS Access) I can see that Access is expecting a data type of "Number" - but if I look at the table that the view queries from originally, the data type is a varchar(5). I expect that Access is looking at the contents of the data and seeing nothing but numbers (values are 10,100,1000, and 10000).</p>
  
  <p>I'm happy to say I didn't design this, so it's not my fault! Hah... but, I do have to support it, so it's up to me to make it work.</p>
  
  <p>So... I think that's all the pertinent info. Let me know if you require more info and I'll edit my question as we go along.</p>
  
  <p>Thanks in advance for any help!</p>
  
  <p>EDIT: More info found:</p>
  
  <p>The table that the view pulls from uses a column with a varchar(5) data type. The reason that field is a varchar(5) is because it's possible to specify a 'speed' of 10, 100, 1000, 10000, or Auto. </p>
  
  <p>Additionaly, it is possible for the populated field in the view to be null.</p>