unknown "blank" values

[Guest]

I have a very large table in which one field should have value 1 or 2, but on
sorting it I find some 600 records that have neither. I've tried to do a
query to select these, and using ="" returns 103 rows. If I add isnull, or
isblank, or even a combination of <1 or >2 I still only get 103 rows. What is
in these fields and how do I identify it??

 

[Allen Browne]

Open your table in design view?
What is the data type of this field?

Try this in the Criteria row of your query under this field. If the type is
number:
Is Null Or Not Between 1 And 2
If Text:
Is Null Or (<> '1' AND <> '2')

 

[Guest]

Thank you for your help.

The field type is text, and tried what you have suggested and still the
query finds 103 rows, any more ideas?

 

[Guest]

Got It!!!!!

For future reference:

Was building query in Design view. If editing test for "isnull" or ="" into
SQL view then all rows returned.

Obviously a useful 'product feature'!!!

Still can't explain why though.