SELECT query field conversion

[Thomas J. Brooks, Jr.]

Hi there...

I'm trying to figure some syntax out. I want a SELECT query where one of
the fields that I have is character in the Access table, but I want to
convert it to numeric for the purposes of the query.

Example: I have an Access table with 2 fields: test, and result. It
contains 7 records as follows:

test result
TEST1 11
TEST1 1
TEST1 3
TEST1 A2
TEST1 10
TEST2 7
TEST2 13

Result is set as a Character, 10 field (because it wouldn't always have
numeric values being entered here). In this particular query, I'm trying to
find specific test records where the numeric value of the result field is >
1. I've ran into "Data Type Mismatch" issues though with a query as
follows:

myquery="SELECT test, result from [labs] WHERE test = 'TEST1" and result >
1"

I've tried using INT(result)>1 and even FORMAT(labs.result,'Standard') > 1
to no avail.

What is weirder than that is if I leave my query as is and change the syntax
to "...and result > int(1)" then it works - kinda. The weird part is when I
put this in, it's like it only finds values in the result field that are in
fact > 1 or that start with the character 1 (so I'd get 4 results). Now, if
I change the query to "...result > int(2)", I only get one result - the
record where result = 3.

I have to be missing something simple. I need to convert the result field
to number, preferably NOT an integer, in the query (I would think the WHERE
clause). How can this be done? And if I can get it to work, I should then
be able to expand the query and make it more complex with INNER JOIN and
HAVING and GROUP BY clauses, right?

Thanks

Tom

 

[Jerry Baker]

Try Cint(Result)

 

[Guest]

You could try adding a criteria that excludes the results that are not
totally numeric, perhaps something like: And [result] not like '*[A-Z]*'

This would allow you to skip the results that are not 'pure' numbers. CInt()
or Val() won't convert a string that starts with a nondigit. (And CInt()
won't convert a string containing a nondigit.)