Cast or Convert

[juster21]

I'm trying to use a sql statement in ADO in a worksheet to return an integer value from numbers which are stored as text.
I've tried the following but neither works...

"SELECT MAX(CAST([ID] AS INT)) FROM [myData$];"

and

"SELECT MAX(CONVERT(INT, [ID])) FROM [myData$];"

Not sure if it's the syntax or what

 

[Norie]

How don't they work?

 

[juster21]

the Convert sql results in an error: "Undefined function "CONVERT" in expression

and

the CAST sql causes an error when trying to open the recordset.

If I use "SELECT COUNT([ID]) FROM [myData$];", it works but I need something more. I need to convert the query result, which is a number stored as text, into a true number

 

[Norie]

There are quite a few conversion functions in VBA.

For this you could use Int, Val, CInt, Lng.