ACC: Flexible Input Mask for Entering 5- or 9-Digit ZIP Codes

Last reviewed: February 18, 1998
Article ID: Q120377
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

This article describes a method of creating 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:

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

          Option Explicit
    

  2. 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
    
    

  3. Type the following in the ValidationRule property of the control that contains the zip code:

          Len([Control name]) = 5 or Len([Control name]) = 9
    

  4. 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.
    

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

          =Zip([<controlname>])
    

    where <controlname> is the name of the control that contains the zip codes.

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."

Keywords          : FmsProp kbusage
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 18, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.