ACC2000: Input Masks Applied to Zip Code Field Ignored with Label Wizard (197588)


The information in this article applies to:

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

SYMPTOMS

When you use the Label Wizard to make mailing labels, and you include a zip code field in the same row as other fields, input masks that you apply 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: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow 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.
  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, click Microsoft Access Help on the Help menu, type Create an input mask to control how data is enteredin a field or control in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about the IIf() function, click the article number below to view the article in the Microsoft Knowledge Base:

209192 ACC2000: How to Use the IIf() (Immediate If) Function

Modification Type:MajorLast Reviewed:6/30/2004
Keywords:kbprb KB197588
©2004 Microsoft Corporation. All rights reserved.