Invalid character value for cast specification (#0) - I know the problem, but not how to resolve it

I'm having a problem with a button in MS Access querying a SQL View that doesn't have a foreign key.

My question is VERY similar to the question found here: MS Access error "ODBC--call failed. Invalid character value for cast specification (#0)" -- 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:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (#0)

Here's where I differ. I have a button in MS Access that does the following:

Private Sub btnMachineCutSheet_Click()
    Dim stDocName As String

    stDocName = "qryCutSheetByMachines"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

The query you see listed there (qryCutSheetByMachines) is a pretty long query, but I've narrowed the problem down to one line. Here's the query:

SELECT 
  vwCutSheet.Network, 
  vwCutSheet.NetworkSpeed, 
  vwCutSheet.Duplex
FROM vwCutSheet 
INNER JOIN local_tblCreateCutSheet 
  ON vwCutSheet.EquipmentID = local_tblCreateCutSheet.EquipmentID;

Keep in mind that all the tables/views are linked tables from a MS SQL 2008 database

The first bit of oddness is that everything works just fine if I remove the vwCutSheet.NetworkSpeed, line. Unfortunately, I need that data.

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 (should views have primary keys?). 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.开发者_开发百科

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).

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.

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.

Thanks in advance for any help!

EDIT: More info found:

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.

Additionaly, it is possible for the populated field in the view to be null.

Not sure if you have permission to work on the SQL side, but if so, try creating your join there and see if it has a problem.

Also, consider changing your stored value 'auto' to something numeric and invalid, like -1, again assuming you can make changes on the sql side.

You may need to do some limiting or conversion on a linked table you can't change before you can join. Instead of the one query you have, you may need 3, if you need to manipulate data in both linked sources before you can join them.

In response to your comment, joining a table linked to SQL server and a local Access table should work, if the data types and values are compatible. If the linked table defined your key field as numeric, but contains text values, like 'auto', it can't work. You'll need to change the linked table to a text field instead of numeric so it can join with the text field in your local table.

I'd say you have a data error. You're storing numbers in a text field, except for when you don't.

If instead you'd define 0 as meaning "Auto" then you could make it a numeric field. Or, use a lookup table and a foreign key value so that 1=10, 2=100, 3=1000, 4=100000 and 0=Auto. If you do that you could use the value as exponent (10^N), and the results would be 1, 10, 100, 1000, 10000, where 1 would be your Auto value.

Or make NULL = Auto, in which case 10^Null would still give you Null (Auto), while all the other values would work.

This would completely design away the problem.

But it does assume you have access to the data table.

As an alternative if you can't change the field's data type, you could create a view that does one of the above for you. To me the easiest thing to do is get rid of the damned text value, "Auto". Of I were doing that, I'd likely convert "Auto" to Null so the result would be a column of numbers with Nulls.