ACC2000: How to Manipulate ZIP Codes in Microsoft Access

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

This article applies only to a Microsoft Access database (.mdb).

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 Hyphens from Existing ZIP Codes
  • Inserting Hyphens in Existing Nine-Digit (ZIP+4) ZIP Codes
  • Formatting ZIP Codes for Use in Reports or Other Applications

MORE INFORMATION

Creating the PostalCodeExample Table Used in This Article

  1. Start Microsoft Access, and then create a new blank database.
  2. In the Database dialog box, double-click Create table in Design view, and then create the following table:
    Table: PostalCodeExample
    Field Name: PCode
    Data Type: Text
  3. Close the table, click Yes to save the table, and then in the Save As dialog box, enter PostalCodeExample. 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.

To restore leading zeros to ZIP codes that have been stored in a Number field, follow these steps:
  1. In the Database dialog box, under Objects, click Tables.
  2. Click PostalCodeExample table, and then click Open. Enter the following five- and nine-digit ZIP code records:
    00345
    023456789
  3. On the View menu, click Design View.
  4. Change the data type of the PCode field to Number, and set the FieldSize property to Long Integer.
  5. Save the table, and then on the View menu click Datasheet View. Note that the leading zeros have been removed.
  6. On the View menu, click Design View and return the data type of the PCode field to Text so that it can store the restored leading zeros in your ZIP codes. Save the table.
  7. Create the following update query based on the PostalCodeExample table.
    Query Name: RestoreLeadingZeros
    Query Type: Update Query

    Field: PCode
    Table: PostalCodeExample
    Update To: IIf(Len([PCode])<6, Format([PCode],"00000"), _
         Format([PCode],"000000000"))
  8. Run the query and confirm the update when prompted. Open the PostalCodeExample table. 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 hyphen after the fifth digit as a literal character. However, when the hyphen is stored as a literal character, it is saved in all ZIP codes, whether it is a five-digit or nine-digit code. The U.S. Postal Service will accept ZIP codes with trailing hyphens, but you may want to avoid trailing hyphens for aesthetic reasons.

If you omit the input mask, you can avoid trailing hyphens in your 5-digit ZIP codes. Then you can either manually include the hyphen when you enter nine-digit (ZIP+4) ZIP codes, or you can use a form to programmatically insert the hyphen 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.

To create a form that will programmatically insert a hyphen when a nine-digit (ZIP+4) ZIP Code is entered, follow these steps:
  1. Create a new form based on the PostalCodeExample table using the AutoForm: Columnar Wizard.
  2. Save the form as frmFormatZIP, and then on the View menu click Design View.
  3. On the View menu, click Code.
  4. Type the following line in the Declarations section of the form's class module sheet:
    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. On the File menu, click Close and Return to Microsoft Access.
  7. On the View menu, click Form View, and then add the following new records:
    01234
    987651011
    WA1 1DP
    1010
    NOTE: When you enter WA1 1DP and 1010, and are prompted to save the ZIP codes as entered, click Yes.
  8. Save and close the form, and then open the PostalCodeExample table. Note that the five-digit ZIP code you entered does not contain a hyphen, the nine-digit ZIP code you entered does contain a hyphen, and the other two records you entered are stored as entered.

Removing Trailing Hyphens from Existing ZIP Codes

You can use an update query to remove trailing hyphens from existing ZIP codes. Follow these steps to remove the trailing hyphens:
  1. Open the PostalCodeExample table and enter the following records:
    12345-
    987654321
    12345-6789
  2. Create the following update query based on the PostalCodeExample table:
    Query Name: RemoveTrailingHyphens
    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. Note that the trailing hyphen after "12345" has been removed.

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

You can use an update query to insert missing hyphens in nine-digit ZIP codes. Follow these steps to insert a hyphen in nine-digit ZIP codes:
  1. Open the PostalCodeExample table and enter the following records:
    12345
    987654321
    12345-6789
  2. Create the following update query based on the PostalCodeExample table:
    Query Name: InsertHyphens
    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. Note that the nine-digit (ZIP+4) ZIP codes all contain hyphens after the fifth digit.

Formatting ZIP Codes for Use in Reports or 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.
  1. Open the PostalCodeExample table, and then enter the following records:
    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 hyphens are not displayed and that a hyphen 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, click Microsoft Access Help on the Help menu, type create a query in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about input masks, click Microsoft Access Help on the Help menu, type create an input mask to control how data in entered in a field or control in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about using Microsoft Access data in Microsoft Word, click Microsoft Access Help on the Help menu, type export a table or query to a Microsoft word mail merge data source file in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 207829 - Last Review: June 28, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbusage KB207829

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