How to I adjust the Zip+4 in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
One option is to execute a query. Something like this:

Update [Table1]
Set [ZipCode] = Left([ZipCode],5) & "-" & Right([ZipCode],4)

This assumes that the entire zipcode is 9 characters in length.

HTH

Rob Mastrostefano

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
 
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
 
Back
Top