convert string to integer?

[djc]

I am dealing with a database that uses a field of datatype text. The field
is being used to collect numeric data now... obviously not what it was
originally intended for. And now I need to create some queries that do math
on this field. Sum() for example. How should I deal with this?

I know vba has easily used conversion functions like Cint(), int() or
something similiar but what about jet SQL? Can I do this directly from my
query? the only thing I could find in the jet sql reference help section was
a CONVERT statement but I could not find the syntax, and I think that it is
intended for ODBC connections only?

anyone?

 

[Allen Browne]

Use a calculated field in your query, e.g.:

MyNum: Val(Nz([MyTextField], 0))

 

[djc]

Thanks for the reply.

1) whats the Nz in your example?

2) So I now realize I can use the vba type conversion functions in SQL
statements. That is great. I successfully used CInt(FieldName) As Expr1 in
the field list part of my SELECT statement. Seemed to work fine. But now how
can I also use this value (the converted value) as part of my WHERE clause?
I tried these 2 ways unsuccsessfully:

a: WHERE CInt(FieldName) > 0
b: WHERE Expr1 > 0

Neither worked (Type conversion error). How can I do this. I have this
string data type that I need to convert to an integer, use as part of
criteria, and possibly do math on it... do I need to use subqueries?

thanks again for your help so far. It is appreciated.

 

[John Spencer (MVP)]

I would try

WHERE CInt(NZ(FieldName,"0")) > 0

If FieldName is null, then you will get an error.

NZ converts NULLs to a specified value.

 

[djc]

Thank you

I would try

WHERE CInt(NZ(FieldName,"0")) > 0

If FieldName is null, then you will get an error.

NZ converts NULLs to a specified value.