safe conversion from string to integer

[Quimbly]

Hello,

I'm no expert when it comes to SQL, so go easy on me. I'm a novice.

Here's the situation: My VB6 code is constructing an SQL query string which will eventually be passed to my Access DB. There is a field in the DB which is text, but which also contains numeric values. Here are some examples of data strings that might be in this field:

0
5
11
X
N/A


I want to be able to search the purely numeric values in this field using logical numeric operators ( i.e. <, <=, >, >=, and = ), but I don't want other non-purely-numeric values returned.

So, if my query was something like

blah blah blah WHERE fieldA > 3

it would return the records with the 5 and 11 in fieldA, and none of the others from the above group, in the result set.

How do I do it?!

Any help is much appeciated.

 

[PHV]

What about this ?
WHERE Val(IsfieldA) > 3


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Quimbly]

The problem with using Val() is that it returns 0 when the input string is non-numeric. So, from my example,

Val(fieldA) where fieldA is "X" or "N/A" will be returned in the results set for the expression:

blah blah blah WHERE Val(fieldA) < 5

..which is not good. I want the records with non-numeric data in fieldA excluded.

How can incorporate is_numeric() is here also. This just a query string! I'm lost.

 

[PHV]

And this ?
WHERE IsNumeric(fieldA) And Val(fieldA) < 5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244