Microsoft KB Archive/120377

= ACC: Flexible Input Mask for Entering Five- or Nine-Digit ZIP Codes =

Article ID: 120377

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q120377



SUMMARY
This article shows you how to create a flexible input mask for entering either 5- or 9-digit ZIP codes in a field on a form.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.



MORE INFORMATION
The method demonstrated in this article uses three user-defined sample Visual Basic (or Access Basic) functions that perform the following tasks:


 * Convert the ZIP code into a text string.
 * Strip the text string of any parentheses, spaces, or dashes.
 * Determine whether the ZIP code contains any alphabetical characters, and if so, terminate the function and alert the user.

To set up the input mask, follow these steps:

  Create a new module and enter the following line in the Declarations section if it is not already there:

     Option Explicit   Enter the following code in the module.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

Function Zip (zipcode As Control)

'Exit if a null is passed to the function. If IsNull(zipcode) Then Exit Function End If       If IsThereAlpha(zipcode) Then Msgbox "Your Zip Code Contains Letters" Exit Function Else 'Strip unwanted characters, leaving only numbers. zipcode = ZStrip(zipcode, "-") zipcode = ZStrip(zipcode, " ") zipcode = ZStrip(zipcode, ")")         zipcode = ZStrip(zipcode, "(")

'Reformat the character string. Select Case Len(zipcode) Case 5 Screen.ActiveControl = Format(zipcode, "@@@@@") Case 9 Screen.ActiveControl = Format(zipcode, "@@@@@-@@@@") Case Else MsgBox "This is not a valid ZIP Code." Screen.ActiveControl = zipcode End Select End If     End Function

Function ZStrip (InZip, StripZip) Do While InStr(InZip, StripZip) InZip = Mid(InZip, 1, InStr(InZip, StripZip) - 1) & Mid _ (InZip, InStr(InZip, StripZip) + 1) Loop ZStrip = InZip End Function

Function IsThereAlpha (zipcode) As Integer Dim Pos, a_char$, Clean Pos = 1 Clean = 0 While (Pos <= Len(zipcode) And (Clean = 0)) a_char$ = Mid(zipcode, Pos, 1) If a_char$ >= "0" And a_char$ <= "9" Then Clean = 0 Else If a_char$ <> "-" Then Clean = 1 End If           Pos = Pos + 1 Wend IsThereAlpha = Clean End Function  Type the following in the ValidationRule property of the control that contains the zip code:

Len([Control name]) = 5 or Len([Control name]) = 9  Type the following for the validation text for the control:

This is not a valid zip code. Zip codes must be 5 or 9 characters in length. Please re-enter the correct zip code.

 Set the AfterUpdate property of the control on your form that contains the ZIP codes to the expression

=Zip([ ])

where is the name of the control that contains the zip codes.

<div class="references_section">