Cannot Run Query with Val()

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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));
 
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;
 
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?
 
Back
Top