Microsoft KB Archive/185970

= ACC97: Input Masks Applied to Zip Code Field Ignored with Label Wizard =

Article ID: 185970

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



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
 Create the Labels Employees: Report by following the steps in the "Steps to Reproduce Behavior" section later in this article. Right-click the last text box in the Labels Employees: Report, and then click Properties on the menu that appears. 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.  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]))  Click OK to close the Expression Builder.</li> Close the property sheet.</li> On the File menu, click Save to save the report.</li> On the File menu, click Print Preview to run the report.</li></ol>

<div class="moreinformation_section">

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
<ol> Open the sample database Northwind.mdb.</li> Open the Employees table in Design view. Add a new field named Zip, and under Data Type, select Text.</li> 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.)</li> In the Input Mask Wizard dialog box, select "Zip Code" from the Input Mask list, and then click Finish.</li> On the View menu, click Datasheet view. When prompted to save the table, click Yes.</li>  Enter the following values into the Zip field for the first five records.

<pre class="fixed_text">      55555-1111 44444-      55555-2222       55455-       55155-                        </li> Close the table.</li> With the Employees table selected, on the Insert menu, click Report.</li> Select Label Wizard from the list and click OK.</li> Accept the defaults for the first two screens by clicking Next.</li>  On the third screen, build the Prototype label by moving the following fields to the specified rows of the label:

<pre class="fixed_text">      First Row:  <FirstName> <LastName> Second Row: <Address> Third Row: <City> <Zip> </li> Click Next.</li> <li>Accept the default of no sort field by clicking Next.</li> <li>Click Finish to create the report.</li></ol>

When the report opens in Print Preview, note that the zip codes are not formatted according to the Input Mask.

<div class="references_section">