Microsoft KB Archive/208949

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

Article ID: 208949

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208949



Moderate: Requires basic macro, coding, and interoperability skills.



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.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.



MORE INFORMATION
The method demonstrated in this article uses three user-defined sample Visual Basic for Applications functions that do the following:
 * 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: 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">