Working with postal codes in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 2002

One type of data that appears frequently in Access is postal code information. Postal codes can take different forms depending on the countries or regions represented in your data. For example, postal codes in Canada may be alphanumeric (for example, T5J 3S9) while postal codes in the United States may be either five digits or nine digits (ZIP+4 format). Fortunately, there are a number of ways to manipulate postal codes in Access, from sorting them to stripping them out of fields containing other address information to using input masks for data entry. This article discusses some of the ways of working with postal codes and provides links to additional information.

ShowManipulating postal codes

You can manipulate postal codes in Access by changing the data type, input mask, or format of a postal code (also known as a ZIP code) field. For detailed information on how to do this, read the Knowledge Base article (Q207829) ACC2000: How to Manipulate ZIP Codes in Microsoft Access located at the Microsoft Developer Network Web site (MSDN). Although the article is written for Access 2000, the techniques described in the article can be applied to Access 2002. For information on manipulating postal codes in Access 97 and earlier versions, read the Knowledge Base article (Q163604) ACC: How to Manipulate ZIP Codes in Microsoft Access .

If you are displaying postal codes in a text box on a form or report by using an input mask and the text box gets its information from more than one field, the input mask will be ignored. For ways to work around this, read the Knowledge Base article (Q197588) ACC2000: Input Masks Applied to Zip Code Field Ignored with Label Wizard . For information on a similar workaround for Access 97, read the Knowledge Base article (Q185970) ACC97: Input Masks Applied to Zip Code Field Ignored with Label Wizard .

ShowSorting postal codes

Depending on how postal codes are entered or imported into your database, the data type of the field containing the postal code information may be Text or Number. The data type of the field determines how Access will sort the data. In general, although this field will usually contain only numbers (some foreign postal codes are exceptions), postal codes shouldn't be treated as numbers as you normally won't be performing numeric calculations on the data. Instead, postal codes should always be stored as text.

When prompted to sort data such as postal codes, Access will try to logically determine the type of sort to perform. For example, the data type of the Postal Codes field in the Employees table of the sample Northwind database is set to Text and contains a mix of US and foreign postal codes. When sorting the data, postal codes containing numeric data are sorted numerically and postal codes containing text characters are sorted alphabetically, all within the same field. To see an example of sorting postal codes in Access, do the following:

  1. Open the sample Northwind database. The Northwind database in Access 2002 can be opened by pointing to Sample Databases from the Help menu.
  2. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
  3. In the New Query dialog box, click Design View, and then click OK.
  4. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
  5. Double-click the Employees table, and then click Close.
  6. Add the EmployeeID, LastName, Address, City, State, and Postal Code fields to the Field row in the design grid.
  7. Under the Field row of the Postal Codes field, specify an ascending sort order.
  8. To view the query's results, click View on the toolbar. Notice that the postal codes are sorted numerically and then alphabetically.

ShowFunctions to retrieve postal codes

There are a number of functions in Access that can be used to retrieve portions of a string such as a postal code from an address. For example, the Left, Right, and Mid functions can be used to extract specific portions of a string. The following examples illustrate sample expressions that you can use to extract a postal code or portions of a postal code from a text string:

  • If the value of the field PostalCode is "98052-6399",
    the expression Left(Trim([PostalCode]),5)
    will retrieve "98052".
  • If the value of the field PostalCode is "Seattle, WA 98052-6399",
    the expression Right([PostalCode],10)
    will retrieve "98052-6399".
  • If the value of the field PostalCode is "Seattle, WA 98052-6399",
    the expression Mid(Trim([PostalCode]),13,5)
    will retrieve "98052".

Examples of these and other functions that can be used to retrieve portions of a string can be found in the Knowledge Base article (Q115915) ACC: Sample Expressions to Extract Portion of Text String .

For more information about programming in Access, visit the Office Developer Center on the Microsoft Developer Network (MSDN).

 
 
Applies to:
Access 2003