Cannot Run Query with Val()

R

Ricky

I have a table with a text field. I want to display those records which
value is greater than 1000. There is some nonnumeric values in the field so
I want to exclude them. I tried the following two queries but Access
displayed error message saying that there is type mismatch. Can anybody help?

SELECT Table1.*
FROM Table1
WHERE (((Val([Field1]))>1000));

SELECT Table1.*
FROM Table1
WHERE (((IsNumeric([Field1]))=True) AND ((Val([Field1]))>1000));
 
T

Todd B.

Try:

SELECT Table1.*
FROM Table1
WHERE IsNumeric([Field1]) = True
AND [Field1]>1000;

OR

SELECT Table1.*
FROM Table1
WHERE IsNumeric([Field1]) = True
AND Csng([Field1])>1000;
 
R

Ricky

I used the first method but the system displayed "Data type mismatch in
criteria expression". I tried the second one but got the message "Invalid
use of null". What is the problem?
 
Top