MS Access Table: Correct for non leading zeroes in

Question

I'm back to Access after an extended leave and am running into some difficulty. I have a table that includes a Zip Code field. Some of the Zip codes that are supplied are 5 digits (52186) and some are the 10 digit type with trailing neighborhood code (77005-1568). However the leading zeroes have not been preserved and I need to reinsert them. For instance (04074) shows up at (4070). The 9 digit zip codes however have included the leading zeroes. I need a way to add the leading zeroes to the 5 digit zip codes. I cannot use a trick like converting to a number and then formatting to "00000" because that creates havoc with the 9 digit zips. Also since I've been absent from Access for so long I'm not sure if I should do this with a function, a query, some type of VBA RecordSet or what. I can't recall the best way to approach. Any and all advice would be greatly appreciated. Thank you!

-Charlie

Answer 1

You can use this expression:

TrueZip = IIf(IsNumeric([Zip]), Format([Zip], "00000"), Right("00" & [Zip], 10))

In a query, it would (in the GUI) be:

TrueZip: IIf(IsNumeric([Zip]),Format([Zip],"00000"),Right("00" & [Zip],10))

As SQL, it would go like this:

Select 
    *,
    IIf(IsNumeric([Zip]),Format([Zip],"00000"),Right("00" & [Zip],10)) As TrueZip
From
    YourTable