Trying to format zip code

[MLH]

Have zip codes in text table field.
Some are 12345 and some are 123456789.

Using Format([ZipCode],"00000-0000") doesn't
produce desired results. Am trying to display
12345-6789 or 12345
depending on whether there's 9-chars or 5-chars
in the [ZipCode] field.

[fredg]

On Wed, 14 Jun 2006 23:00:09 -0400, MLH wrote:

Have zip codes in text table field.
Some are 12345 and some are 123456789.

Using Format([ZipCode],"00000-0000") doesn't
produce desired results. Am trying to display
12345-6789 or 12345
depending on whether there's 9-chars or 5-chars
in the [ZipCode] field.

Use an unbound text control.
Set it's control source to:
=IIf(Len([Zip])>=6, Left([Zip],5) & "-" & Right([Zip],4),[Zip[)
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[pietlinden]

MLH wrote:

Have zip codes in text table field.
Some are 12345 and some are 123456789.

Using Format([ZipCode],"00000-0000") doesn't
produce desired results. Am trying to display
12345-6789 or 12345
depending on whether there's 9-chars or 5-chars
in the [ZipCode] field.

base your report on a query or use an unbound control.
iif(Len$(ZipCode)=5,ZipCode,left$(ZipCode,5)&"-"&right$(Zipcode,4))

[MLH]

Well, that's another way.

I could use this...
IIf(Len([ZipCode])>5,Format$([ZipCode],"00000-0000"),Format$([ZipCode],"00000"))

But I was hoping to accomplish it all within the confines
of the Format function. Perhaps its not possible.

[Larry Linson]

"MLH" wrote

Have zip codes in text table field.
Some are 12345 and some are 123456789.

Using Format([ZipCode],"00000-0000") doesn't
produce desired results. Am trying to display
12345-6789 or 12345
depending on whether there's 9-chars or 5-chars
in the [ZipCode] field.

The following function (without error handling or content checking other
than length, for simplicity) seems to work OK for me on a few ZIP codes that
I passed it, in string form.

Function FmtZIP(pstrZIP As String) As String

If Len(pstrZIP) = 5 Then
FmtZIP = Format(pstrZIP, "@@@@@")
ElseIf Len(pstrZIP) = 9 Then
FmtZIP = Format(pstrZIP, "@@@@@\-@@@@")
Else
FmtZIP = "*** ERROR: " & pstrZIP & " is not a valid ZIP"
End If

End Function

Larry Linson
Microsoft Access MVP