ACC97: Input Masks Applied to Zip Code Field Ignored with Label Wizard (185970)


The information in this article applies to:

  • Microsoft Access 97
This article was previously published under Q185970
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you use the Label Wizard to make mailing labels and include a zip code field in the same row as other fields, input masks applied to the zip code field are ignored.

CAUSE

Input masks can only apply to a single field in a single text box. When a text box on a report contains more than one field, the input masks for individual fields are ignored.

RESOLUTION

Use an immediate if (IIf) statement to format the output for the zip code field. You can change the ControlSource property of the text box that contains the zip code field in the report's Design view.

Using an Immediate IF to Format a Text Field


  1. Create the Labels Employees: Report by following the steps in the "Steps to Reproduce Behavior" section later in this article.
  2. Right-click the last text box in the Labels Employees: Report, and then click Properties on the menu that appears.
  3. In the Properties dialog box, click the Data tab, click in the ControlSource property box, and then click the Build button to the right of the ControlSource property box to open the Expression Builder.
  4. In the Expression Builder, change the existing formula 
         =Trim([City] & " " & [zip])

    to: 
         =Trim([City] & " " & IIf(Len([zip])=9,Left([zip],5) & "-" &
        Right([zip],4),[zip]))
  5. Click OK to close the Expression Builder.
  6. Close the property sheet.
  7. On the File menu, click Save to save the report.
  8. On the File menu, click Print Preview to run the report.

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Steps to Reproduce Behavior


  1. Open the sample database Northwind.mdb.
  2. Open the Employees table in Design view. Add a new field named Zip, and under Data Type, select Text.
  3. On the General tab, click in the Input Mask row and click the Build button to the right of the row. (The button with three dots on it.)
  4. In the Input Mask Wizard dialog box, select "Zip Code" from the Input Mask list, and then click Finish.
  5. On the View menu, click Datasheet view. When prompted to save the table, click Yes.
  6. Enter the following values into the Zip field for the first five records. 
           55555-1111
       44444-
       55555-2222
       55455-
       55155-
  7. Close the table.
  8. With the Employees table selected, on the Insert menu, click Report.
  9. Select Label Wizard from the list and click OK.
  10. Accept the defaults for the first two screens by clicking Next.
  11. On the third screen, build the Prototype label by moving the following fields to the specified rows of the label: 
           First Row:  <FirstName> <LastName>
       Second Row: <Address>
       Third Row:  <City> <Zip>
  12. Click Next.
  13. Accept the default of no sort field by clicking Next.
  14. Click Finish to create the report.
When the report opens in Print Preview, note that the zip codes are not formatted according to the Input Mask.

REFERENCES

For more information about using input masks, search the Help Index for "input masks," and then "Create an input mask to control how data is entered in a field or control."

For more information about the IIf() function, see the following Knowledge Base article:

106288 ACC: How to Use the IIf() (Immediate If) Function
Modification Type:MajorLast Reviewed:9/26/2003
Keywords:kbprb KB185970
©2003 Microsoft Corporation. All rights reserved.