How to I adjust the Zip+4 in Access?

K

KyCoal41501

I need to insert the hyphen in my zip codes, that I have imported from Excel.
The numbers are all together. How do I do that?
 
S

Sprinks

Assuming they exist as Text in Excel, import them into Access, and use an
Update query to parse them.

If you are *sure* they are all nine characters long, you can use the
following as the "Update To:" expression (I'm assuming the field is named
'Zip'):

Left(Trim([Zip]),5) & "-" & Right(Trim([Zip]),4)

If you have a mixture of 5 & 9 characters, a custom function is easier than
a nested IIf:

Public Function ConvertZip(varRawZip as Variant)

Dim strTrimRawZip As String
strTrimRawZip = Trim(varRawZip)

If Len(strTrimRawZip) = 9
ConvertZip = Left(strTrimRawZip,5) & "-" & Right(strTrimRawZip,4)
Else
ConvertZip = strTrimRawZip
End If

End Function

Cut and paste the function to a public module in your database (from the
Module tab, not a form module).

Then use the following expression for your Update query:

= ConvertZip([Zip])

Hope that helps.
Sprinks
 
Top