I am trying to format the zip code field to display the +4 format but if I use the @@@@@-@@@@ format I get a trailing - for the zip code entries that only have the first 5 digits. I am in the Beginner levels of Access so I am not using any VBA yet. Is there a way to format the zip to display only the 5 digits if that is all that is available and not have the hyphen hanging there?

---

Reply from Richard Rost:

You can't do this with a simple format or input mask. It's going to involve the IIF and LEN functions. Store the ZIP code in your table without any formatting. You then have either a 5 or 9 digit text value (if you're only dealing with the US). You can then create a calculated query field like this:

NewZIP: IIF(Len(ZIP)=5,Format(ZIP,"@@@@@"),IIF(Len(ZIP)=9,Format(ZIP,"@@@@@-@@@@"),ZIP))

What this says is basically this:

IF the ZIP is 5 digits long, use the format @@@@@
ELSE IF the ZIP is 9 digits long, use the format @@@@@-@@@@
OTHERWISE just display the ZIP unformatted

Now once you have this field built, you can use NewZIP in any forms or reports where you need to see it formatted.