ACC: How to Manipulate ZIP Codes in Microsoft Access

Article translations Article translations
Article ID: 163604 - View products that this article applies to.
This article was previously published under Q163604
Moderate: Requires basic macro, coding, and interoperability skills.

Expand all | Collapse all

On This Page

SUMMARY

You can manipulate ZIP codes in Microsoft Access by changing the data type, input mask, or format of a ZIP Code field. This article shows you how to manipulate ZIP codes that are already stored in a table and how to format ZIP codes when you enter them on a form.

This article contains the following sections:
  • Creating the PostalCodeExample Table Used in this Article
  • Restoring Leading Zeros That Are Missing
  • Saving Formatted ZIP Codes
  • Removing Trailing Dashes from Existing ZIP Codes
  • Inserting Dashes in Existing Nine-Digit (ZIP+4) ZIP Codes
  • Formatting ZIP Codes for Use in Reports or for Use by Other Applications
Portions of this article assume that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

Creating the PostalCodeExample Table Used in this Article

The following example table is used in this article. Follow these steps to create the table:
  1. Start Microsoft Access and create a new blank database.
  2. Create the following new table in Design view:
           Table: PostalCodeExample
           ------------------------
           Field Name: PCode
           Data Type: Text
    						
  3. Save the PostalCodeExample table. Do not create a primary key.

Restoring Leading Zeros That Are Missing

Store ZIP codes in a Text field in your table rather than in a Number field. Microsoft Access follows standard mathematical conventions when storing numeric data, so if you enter a ZIP code that contains leading zeros in a Number field, Microsoft Access removes the leading zeros when you save the data. For example, if you enter 01002 in a Number field, Microsoft Access stores the number as 1002.

Follow these steps to restore leading zeros to ZIP codes that have been stored in a Number field:
  1. Open the PostalCodeExample table, which you just created, in Datasheet view and enter the following five- and nine-digit ZIP Code records for the PCode field:
          00345
          023456789
    						
  2. Switch the PostalCodeExample table to Design view.
  3. Change the data type of the PCode field to Number, and set the FieldSize property to Long Integer.
  4. Save the table and switch it to Datasheet view. Note that the leading zeros have been removed.
  5. Switch the PostalCodeExample table to Design view and change the data type of the PCode field to Text again so that it can store the restored leading zeros in your ZIP codes. Save the table.
  6. Create the following update query based on the PostalCodeExample table.

    NOTE: In this example, the underscore (_) at the end of the Update To line is used as a line-continuation character. Remove the underscore from the end of the line when you create this query.
          Query Name: RestoreLeadingZeros
          Query Type: Update Query
    
          Field: PCode
          Table: PostalCodeExample
          Update To: IIf(Len([PCode])<6, Format([PCode],"00000"), _
                         Format([PCode],"000000000"))
    						
  7. Run the query and confirm the update when prompted. Open the PostalCodeExample table in Datasheet view. Note that the leading zeros have been restored.

Saving Formatted ZIP Codes

The Input Mask Wizard helps you to create an input mask for ZIP codes that stores a dash after the fifth digit as a literal character. However, when the dash is stored as a literal character, it is saved in all ZIP codes, whether it is a five- or nine-digit code. The U.S. Postal Service will accept ZIP codes with trailing dashes, but you may want to avoid trailing dashes for aesthetic reasons.

If you omit the input mask, you can avoid trailing dashes in your 5-digit ZIP codes. Then you can either manually include the dash when you enter nine-digit (ZIP+4) ZIP codes, or you can use a form to programmatically insert the dash after a nine-digit (ZIP+4) ZIP code has been entered. In either case, you also gain the ability to enter foreign postal codes in your table.

Follow these steps to create a form that will programmatically insert a dash when a nine-digit (ZIP+4) ZIP Code is entered:
  1. Create a new form based on the PostalCodeExample table using the AutoForm: Columnar Wizard in Microsoft Access 97 and 7.0, or the AutoForm Wizard in Microsoft Access 2.0.
  2. Save the form as frmFormatZIP, and then switch it to Design view.
  3. On the View menu, click Code.
  4. Type the following line in the Declarations section of the form's class module:

    Dim mvarZip
  5. Type the following procedures:
          Private Sub PCode_AfterUpdate()
             If IsEmpty(mvarZip) Then Exit Sub
             If Len(mvarZip) = 6 Then
                Screen.ActiveControl = Left(mvarZip, Len(mvarZip)-1)
             Else
                Screen.ActiveControl = Format(mvarZip, "@@@@@-@@@@")
             End If
             mvarZip = Empty
          End Sub
    
          Private Sub PCode_BeforeUpdate(Cancel As Integer)
             Dim ctlZip As Control
             Dim strTitle As String
             Dim strMsg As String
             Const cYesNoButtons = 4
             Const cNoChosen = 7
    
             mvarZip = Empty
             Set ctlZip = Screen.ActiveControl
    
             If ctlZip Like "#####-####" Or ctlZip Like "#####" Then
                Exit Sub
             ElseIf ctlZip Like "#########" Or ctlZip Like "#####-" Then
                mvarZip = ctlZip
             Else
                strTitle = "Not a ZIP Code."
                strMsg = "Save as entered?"
                If MsgBox(strMsg, cYesNoButtons, strTitle) = cNoChosen Then
                   Cancel = True
                End If
             End If
          End Sub
    						
  6. Switch the form to Form view, and add the following new records for the Pcode field:
           01234
           987651011
           WA1 1DP
           1010
    						

    Note that when you enter WA1 1DP and 1010, you are prompted whether you want to save the ZIP Codes as entered. Click Yes.
  7. Save and close the frmFormatZIP form, and then open the PostalCodeExample table in Datasheet view. Note that the five-digit ZIP code you entered does not contain a dash, the nine-digit ZIP code you entered does contain a dash, and the other two records you entered are stored as entered.

Removing Trailing Dashes from Existing ZIP Codes

You can use an update query to remove trailing dashes from existing ZIP codes. Follow these steps to remove the trailing dashes:
  1. Open the PostalCodeExample table in Datasheet view and enter the following records for the Pcode field:
          12345-
          987654321
          12345-6789
    						
  2. Create the following update query in based on the PostalCodeExample table:
           Query Name: RemoveTrailingDashes
           Query Type: Update Query
    
           Field: [PCode] Like "#####-"
           Criteria: True
    
           Field: PCode
           Table: PostalCodeExample
           Update To: Left([PCode], Len([PCode])-1)
    						
  3. Run the query and confirm the update when prompted.
  4. Open the PostalCodeExample table in Datasheet view. Note that the trailing dash after 12345 has been removed.

Inserting Dashes in Existing Nine-Digit (ZIP+4) ZIP Codes

You can use an update query to insert missing dashes in nine-digit ZIP codes. Follow these steps to insert a dash in nine-digit ZIP codes:
  1. Open the PostalCodeExample table in Datasheet view and enter the following records for the Pcode field:
           12345
           987654321
           12345-6789
    						
  2. Create the following update query based on the PostalCodeExample table:
           Query Name: InsertDashes
           Query Type: Update Query
    
           Field: [PCode] Like "#########"
           Criteria: True
    
           Field: PCode
           Update To: Format([PCode],"@@@@@-@@@@")
    						
  3. Run the query and confirm the update when prompted.
  4. Open the PostalCodeExample table in Datasheet view. Note that the nine-digit (ZIP+4) ZIP codes all contain dashes after the fifth digit.

Formatting ZIP Codes for Use in Reports or for Use by Other Applications

You can use your ZIP codes in a Microsoft Access report or in another application (such as Microsoft Word) without modification if they already are saved in the desired format. Earlier sections in this article demonstrate how to change the way your existing ZIP code formats are stored in the table. However, if you want to format ZIP codes without changing the way they are stored, you can follow these steps to temporarily create the ZIP code format you want to use.

Follow these steps to display formatted ZIP codes in a select query:
  1. Open the PostalCodeExample table in Datasheet view and enter the following records for the PCode field:
           54321
           12345-
           987654321
           12345-6789
    						
  2. Create the following query based on the PostalCodeExample table.

    NOTE: In this example, the underscore (_) at the end of the Field line is used as a line-continuation character. Remove the underscore from the end of the line when you create this query.
           Query Name: FormatZIPCodes
           Query Type: Select Query
    
           Field: Postal Code: IIf([PCode] Like "#####-", Left([PCode], _
                               Len([PCode])-1), IIf([PCode] Like _
                               "#########",Format([PCode], "@@@@@-@@@@"), _
                               [PCode]))
    						
  3. Run the query. Note that trailing dashes are not displayed and that a dash appears after the fifth digit in nine-digit ZIP codes. You can use this query instead of your table when you create your report or share the data with another application. This select query displays formatted ZIP codes but it does not alter your table's existing ZIP code data.

REFERENCES

For more information about update queries, search the Help Index for "update queries."

For more information about the Format() function, search the Help Index for "Format function" or "Format$ function."

For more information about input masks, search the Help Index for "input masks."

For more information about using Microsoft Access data in Microsoft Word, search the Help Index for "Word (Microsoft)."

Properties

Article ID: 163604 - Last Review: January 19, 2007 - Revision: 2.1
APPLIES TO
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Keywords: 
kbhowto kbusage KB163604
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com