SQL statement works in Access but not via ODBC (1 Viewer)

[FrankS800]

I want to select rows in a table using a relational operator on a character type column. I want to do a numeric comparison. I can do that within Access in the following way:
SELECT DISTINCT colname FROM table WHERE VAL(colname & "") > 12
When I try to do that using the Access ODBC driver (64-bit), it fails with the message:
ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Can anybody give me a hint how to make it work or is this an ODBC issue

 

[cheekybuddha]

Is this a pass-through?

Your server may not recognise Val()

Also, concatenation operator in SQLServer is "+", not "&" - use COALESCE() to handle NULLs

(Hint: Use real table/field names in your code when you post it - pseudo code makes it more difficult to advise!)

 

[cheekybuddha]

Also, are you trying to use ODBC to connect to an Access backend?

 

[FrankS800]

Is this a pass-through?

Your server may not recognise Val()

Also, concatenation operator in SQLServer is "+", not "&" - use COALESCE() to handle NULLs

(Hint: Use real table/field names in your code when you post it - pseudo code makes it more difficult to advise!)

No, it's not a pass-through. I create an ODBC connection to the Access database using the VB.Net OdbcConnection class. Normally the SQL statements that work inside access also work in the ODBC connection. I know there are discrepancies for instance using '%' and '*' in a LIKE clause, I wonder if that might also be the case here.

 

[cheekybuddha]

No, it's not a pass-through. I create an ODBC connection to the Access database using the VB.Net OdbcConnection class.

OK, I suspect it's Val() in that case.

Val() is a VBA function - is it available in your .Net app?

I wonder whether you can get away with implicit type casting:

SELECT DISTINCT colname FROM table WHERE colname > 12

If colname is NULL the record won't be returned anyway.

 

[FrankS800]

The SQL statement is passed directly to the ODBC driver. Microsoft ODBC drivers only support SQL, be it for Access, Excel or even text files. For Excel and text files there are known limitations as these are not natively SQL based. Access is however SQL based and therefore I would think the ODBC driver would accept the same SQL statement as in Access itself. Just wanted to know if anybody had some experience with it.

 

[cheekybuddha]

Did you try with the simplified statement?