"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