After a query is run ... change 9 diget zip to 5 diget zip

[Jean]

How do I change 123456789 to 12345-6789 or 12345 by using
criteria in the query's design view?

I have a database with 26,000 entries. In these there
are zip codes (9 diget zip) ...

I've culled the list down to 17,000 but can't find that
culled table ... so I can't just go back and change the
field size from 9 to 5 ...

I have a query with the 9 diget zip, however, when
purchased from the Postal Service, they did not put the
dash in the zip, so it reads as ######### (but I need to
either display it as #####-####)or just the first 5
digets.

Please respond to my e-mail at (e-mail address removed)

Thanks for your time.

 

[[MVP] S. Clark]

1. Add a new field to the table called newzip (prevent overwriting original
data)
2. In query: IIf(len([zip]=9, [newzip] = Left$(zip,5) & "-" & right$(zip,4))
3. compare zip to newzip. If satisfied, delete zip and rename newzip to
zip.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[John Spencer (MVP)]

Just the first five digits, use the left function

Left(YourZIP & "",5)

Formatted with a dash or not

IIF(Len(YourZIP & "")=9,Format(YourZip,"@@@@@-@@@@",YourZip)