Solved 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?

 

[The_Doc_Man]

SELECT DISTINCT colname FROM table WHERE VAL(colname & "") > 12

Try:

SELECT DISTINCT colname FROM table WHERE ( colname > 12 ) AND ( IS NOT NULL( colname ) )

 

[cheekybuddha]

( IS NOT NULL( colname ) )

Hmmm...

Feels like you're confusing/conflating NOT IsNull(colname) (SQL/VBA mix) and colname IS NOT NULL (SQL)

(... but the idea is good! )

 

[Pat Hartman]

Val() is not an SQL function. it is a VBA function. When you run queries against ACE BE's using ODBC, you are limited to the functions available to SQL. I don't have a list handy but you should be able to find one in the documentation.

 

[The_Doc_Man]

Hmmm...

Feels like you're confusing/conflating NOT IsNull(colname) (SQL/VBA mix) and colname IS NOT NULL (SQL)

(... but the idea is good! )

I always get those pesky usages backwards...

 

[FrankS800]

Created a small test application for this: The basic request is thus: I have a table named RATINGS with a text type field named RATING. Field values can be either NULL numeric and non-numeric. I want to return the row where rating is larger or equal to a target value but is nearest to the target value. So if I have values 3, 4, 5, 7, 8 and my target value is 6 it should return 7.

In Access the following works:

SELECT TOP 1 rating FROM ratings WHERE ISNUMERIC(rating) and VAL(rating & "") > 6 ORDER BY VAL(rating & "")

In ODBC the first part works, with a slightly different syntax. The result rows are however in random order.

SELECT rating FROM ratings WHERE ISNUMERIC(rating) and VAL(rating)>6

Adding the order by clause however doesn't work giving a data type mismatch issue. Either I am missing something or the ODBC driver is the culprit.

SELECT rating FROM ratings WHERE ISNUMERIC(rating) and VAL(rating)>6 ORDER BY val(rating)

Appreciate all the help sofar

 

[arnelgp]

you can scrap the IsNumeric part.

SELECT rating FROM ratings WHERE VAL(rating & "")>6 ORDER BY val(rating & "")

 

[FrankS800]

Finally got it working in ODBC:

SELECT top 1 rating FROM ratings WHERE ISNUMERIC (rating) and VAL(rating & '""') > 6 ORDER BY VAL(rating & '""')

The trick is to put the double quotes inside single quotes. The ISNUMERIC is required as otherwise a value like '20-50' might be returned. Thanks for all the input.

 

[DickyP]

I know the solutions to this thread is to avoid UNION but it is worth pointing out that ODBC Drivers are notorious for not handling UNION. Test the usage against Access rear end, but to use against the target database use Pass through queries.

 

[Pat Hartman]

Why are you concatenating a ZLS to a numeric value? If you mix numbers and letters, in a sort, it will appear scrambled but it isn't.