Return based on Criteria

SORI

New member
Local time
Today, 09:05
Joined
Aug 27, 2013
Messages
6
I have a table with Zip CODE field. I uploaded my information to this table form an excel file. There were over 120K rows of data so no way i could do this in excel.

So I found that some of the ZIP codes are either blank or have less than 5 digits.

Can I build a a query with the ZIP code field that will return all zip code records with less than 5 characters or blank. I'd like to repair these zip codes and a update query will be my next step.

I repeat excel is not an option because when I try to sort by the Zip Code column my computer explodes (okay not literally) :eek::eek:

Thanks for your help.:banghead:
 
This SQL should return all the records with an invalid zip code per your definition:

Code:
SELECT YourTableName.*, IIf(IsNull([ZIP]) Or Len([ZIP])<5,1,0) AS InValid
FROM YourTableName
WHERE (((IIf(IsNull([ZIP]) Or Len([ZIP])<5,1,0))=1));

Replace instances of 'YourTableName' with the name of your table, and 'ZIP' with the name of your zip code field.
 
THanks this works perfect.

:banghead::banghead:
 
Try this . create a new field in the query FaultyZip:Len([zipcode])
in the criteria <5 or is null

Hope this works for you. i am not a pro so there might well be a better solution

Regards

Oops i was too late
 
Last edited:

Users who are viewing this thread

Back
Top Bottom