val() error after converting Access-> SQL Server 1

[dlkraft]

What is the correct syntax, please?

I have a varchar field that, in Access, could be converted to a number within queries, for ex:

WHERE VAL(ROOM) < 0200

Now that I've converted to SQL Server, my queries are throwing an error:

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]'VAL' is not a recognized function name.
SQL = &quot;SELECT * from datasource WHERE 0=0 AND VAL(ROOM) < 0200 Order by last, first&quot;

 

[philhege]

You'll need to use a SQL-compliant function.

Look into CAST or CONVERT in Books On-Line.

HTH,
PH

 

[dlkraft]

Thanks Phil,

I will do that. Meanwhile, as ugly a concept as it may seem, I ended up comparing the numbers as varchar fields of the same length.

Because the fields are indexed in SQL Server, the response was acceptable.

Next time I'll try CAST or CONVERT.

Thanks again,

Dave

 

[neofactor]

Are you sure the ROOM variable is indeed a 'int' integer?
It might be a text field or varchar

If it is a int, then why are you using 0200?
That sound like a text field with a leading zero.. int don't keep the leading zero

let me know.