Querys and filters

  • Thread starter David Meyers via AccessMonster.com
  • Start date
D

David Meyers via AccessMonster.com

When I filter my 985,000 Records by Zip code I see I have 100,000 records
without zip codes. When I Query this table for "" Nothing in the Zip code
field It only shows 2,800 records. Why? this also happens in othe fields as
Well. How to I fix my Query to find all "" nothings in a fields?
 
D

Douglas J. Steele

Presumably the records without Zip codes have Null, rather than a
zero-length string (""), as their value.

Use WHERE ZipCode IS NULL as your criteria.
 
J

John Spencer (MVP)

Probably have a null value in some of the records and a zero-length string in
others. Try one of the following solutions.

Calculated field:

Field: ZipBlank: NZ([ZipCode],"")
Criteria: ""

That will be slow since it cannot use anyu index that you have on the field.
Faster will be to search for both variants of the criteria

Field: [ZipCode]
Criteria (Line1): = ""
Criteria (Line2): Is Null
 
Top