Validate input such as ZIP codes and postal codes

<< Click to Display Table of Contents >>

Navigation:  Access Controls >

Validate input such as ZIP codes and postal codes

Doug Steele        

In Access Answers, Doug Steele addresses commonly asked questions from Access developers. This month, he looks at using Regular Expressions in Access, as well as problems dealing with time values .
 

Is there a simple way to validate input such as ZIP codes and postal codes?

While it's possible to write a function that uses string functions such as Left, Mid, and InStr to validate your input, it may be more appropriate to take advantage of the Regular Expression capability available through the object library created, originally, for VBScript. Postal codes are generally great candidates for Regular Expressions. For instance, US ZIP codes are either "NNNNN" or "NNNNN-NNNN", while Canadian postal codes have the form "ANA NAN" (where A is an alphabetic character, and N is a numeric character).

Regular Expressions provide a concise, flexible notation for finding specific patterns of text, including complex patterns. If you can define a rule for how your text is supposed to appear, you can generally use Regular Expressions to validate it. Unfortunately, trying to explain how to define a Regular Expression pattern is beyond the scope of what I can present in this column. The following URLs have good material on the topic:

http://msdn.microsoft.com/library/en-us/dnclinic/html/scripting051099.asp

http://msdn.microsoft.com/library/en-us/script56/html/js56reconIntroductionToRegularExpressions.asp

http://etext.lib.virginia.edu/helpsheets/regex.html

If this isn't enough for you, do a Google search on "Regular Expressions" (including the quotes). You'll get literally thousands of other possible resources!

Now, I'm not an expert in Regular Expressions by any stretch of the imagination, but I've found that these regular expression patterns correspond to five-digit ZIP codes, nine-digit ZIP codes, and Canadian postal codes, respectively:

• \d{5}

• \d{5}\-\d{4}

• [A-Z]\d[A-Z] \d[A-Z]\d

Actually, I'm lying. Canadian postal codes never start with the letters D, F, I, O, Q, U, W, or Z, so the pattern should really be "[ABCEGHJKLMNPRSTVXY]\d[A-Z] \d[A-Z]\d". Problem is, that's more characters than they allow me on a line of code in this column.

I generally try to avoid adding many external references to my applications, due to the problems that can arise if the exact same reference doesn't exist on the user's machine (but sometimes it may be unavoidable). Fortunately, it's possible to use Regular Expressions in Access without having to set a reference by using CreateObject and late binding. That's what the following code does. In it, I explicitly trap for Error 429 ("ActiveX component can't create object") because that's the error that will occur if, for some reason, the necessary DLL doesn't exist on the user's workstation:

Public Const gcUSZipCode5 As String = "^\d{5}$"

Public Const gcUSZipCode9 As String = "^\d{5}\-\d{4}$"

Public Const gcCdnPostCode As String = _

                       "\b[A-Z]\d[A-Z] \d[A-Z]\d\b"

Public Const gcPostal as string = _

  "(" & gcUSZipCode5 & ")|(" & gcUSZipCode9 & _

  ")|(" & gcCdnPostCode & ")"

Function ValidString( _

   StringToCheck As Variant, _

   PatternToUse As String, _

   Optional CaseSensitive As Boolean = True) _

 As Boolean

On Error GoTo Err_ValidString

Dim reCurr As Object

  If Len(StringToCheck & vbNullString) > 0 Then

    Set reCurr = CreateObject("VBScript.RegExp")

    reCurr.Pattern = PatternToUse

    reCurr.IgnoreCase = Not CaseSensitive

    ValidString = reCurr.Test(StringToCheck)

  Else

    ValidString = False

  End If

End_ValidString:

  Set reCurr = Nothing

  Exit Function

Err_ValidString:

  If Err.Number = 429 Then

    Err.Raise 429, _

      "ValidString(" & StringToCheck & ", " & _

      PatternToUse & ", " & CaseSensitive & ")", _

      "Regular Expressions not available"

  Else

    Err.Raise Err.Number, _

      "ValidString(" & StringToCheck & ", " & _

      PatternToUse & ", " & CaseSensitive & ")", _

      Err.Description

  End If

  Resume End_ValidString

End Function

Here are some sample results from running this function in the Debug window:

?ValidString("12345", gcPostal)      'Valid US zip code

True

?ValidString("1234", gcPostal)       'Too few digits

False

?ValidString("123456", gcPostal)     'Too many digits

False

?ValidString("123456789", gcPostal)  'No dash

False

?ValidString("12345-6789", gcPostal) 'Valid US zip code

True

?ValidString("1234-56789", gcPostal) 'Dash wrong

False

?ValidString("M3B 2Y5", gcPostal)    'Valid Canadian code

True

?ValidString("m3b 2y5", gcPostal)    'Lower case invalid

False

?ValidString("M3B2Y5", gcPostal)     'No space

False

?ValidString("M3B 25Y", gcPostal)    'Not ANA NAN

False

Now that you have this function, how do you use it? The easiest way is to call the function in the BeforeUpdate event of whatever control on your form accepts the input you want to validate. Something like the following will prevent you from saving the data your user entered if the data isn't valid:

Private Sub txtZipCode_BeforeUpdate(Cancel As Integer)

Dim strMessage as String

  If Not ValidString( _

                Me!txtZipCode, _

                gcPostal) Then

    strMessage = Me!txtZipCode & _

          " is not valid." & vbCrLf _

          "Save it anyhow?"

    Select Case MsgBox(strMessage, _

           vbYesNo + vbQuestion + vbDefaultButton2)

      Case vbYes

        Cancel = False

      Case vbNo

        Cancel = True

      Case Else

        Cancel = True

    End Select

  End If

End Sub

This code flags when an entry isn't valid, but gives the user the option of saving it anyhow. Play with it how you like. Unfortunately, you can't use global variables in queries, so you can't use the ValidString function in a query directly. However, it's simple to create another function, ValidPostalCode, which uses ValidString:

Function ValidPostalCode(StringToCheck As Variant)

    ValidPostalCode = _

          ValidString(StringToCheck, gcPostal)

End Function

As an example, you can use the ValidPostalCode function in a SQL statement that returns all customer records for which the postal code or ZIP code is incorrect (and you won't run into problems with records where the value for the ZipTX field is Null):

SELECT CustomerID, CustomerNM, Address1TX, Address2TX,

       CityTX, StateTX, ZipTX FROM Customer

WHERE ValidPostalCode([ZipTX]) = False

Regular Expressions can be used for more than just validation. You can use them to give you an extended InStr capability. What happens if you have a situation where you want to know whether a particular pattern occurs in a string and, if it does, where in the string the pattern is located? For instance, what if you have a string that contains the entire address for a customer, and you need to extract the postal code from it? This function will do the job:

Function ExtendedInStr(StringToSearch As Variant, _

    PatternToUse As String, _

    Optional CaseSensitive As Boolean = False) _

  As Long

On Error GoTo Err_ExtendedInStr

Dim reCurr As Object

Dim maCurr As Object

  Set reCurr = CreateObject("VBScript.RegExp")

  reCurr.Pattern = PatternToUse

  reCurr.IgnoreCase = Not CaseSensitive

  reCurr.Global = True

  For Each maCurr In reCurr.Execute(StringToSearch)

    ExtendedInStr = (maCurr.FirstIndex + 1)

    Exit For

  Next maCurr

End_ExtendedInStr:

  Set maCurr = Nothing

  Set reCurr = Nothing

  Exit Function

Err_ExtendedInStr:

'error code as before

End Function

This function will let you find where in a string a postal code appears. Here's the function in action in the Debug window:

?ExtendedInStr( _

  "123 Main St, Toronto, ON  M3B 2Y5", _

  gcCdnPostCode)

27

Looking at the string "123 Main St, Toronto, ON M3B 2Y5", you'll see that the postal code does, in fact, start in column 27 (there are two spaces between ON and the postal code). The function will return 0 if no text matching the pattern is found.

It's also possible to do corrections using Regular Expressions. For example, if you wanted your function to correct Canadian postal codes that were close to being correct (not uppercase, for example, or forgetting the space), you can use something like this:

Function FixCdnPostalCode(PostalCodeIn As Variant) _

                    As String

Dim reCurr As Object

Dim maCurr As Object

Dim macCurr As Object

Dim intLoop As Integer

Dim strFixed As String

If Len(Trim$(PostalCodeIn & vbNullString)) > 0 Then

  Set reCurr = CreateObject("VBScript.RegExp")

  reCurr.Pattern = _

       "([A-Z]\d[A-Z])(\s*)(\d[A-Z]\d)(\.*)"

  reCurr.IgnoreCase = True

  Set macCurr = reCurr.Execute(PostalCodeIn)

  If macCurr.Count > 0 Then

      strFixed = UCase$( _

        macCurr(0).SubMatches.Item(0) & _

        " " & macCurr(0).SubMatches.Item(2))

  Else

      strFixed = PostalCodeIn & " is invalid."

  End If

End If

  FixCdnPostalCode = strFixed

End Function

Here are some samples of what this function can do:

?FixCdnPostalCode("R3T 3R8")   'Valid Canadian

R3T 3R8

?FixCdnPostalCode("r3t 3r8")   'Lower case

R3T 3R8

?FixCdnPostalCode("R3T3R8")    'Space omitted

R3T 3R8

?FixCdnPostalCode("r3t3r8")    'Lower case

R3T 3R8                        'and space omitted

?FixCdnPostalCode("R3T R38")   'Not ANA NAN

R3T R38 is invalid.

?FixCdnPostalCode("R3T3R81@")  'Extra stuff at end

R3T 3R8

You may not want that last bit of functionality that trims off trailing characters. Changing the pattern easily turns this off. I used this code:

reCurr.Pattern = "([A-Z]\d[A-Z])(\s*)(\d[A-Z]\d)(\.*)"

You could use this code:

reCurr.Pattern = "\b([A-Z]\d[A-Z])(\s*)(\d[A-Z]\d)\b"

With the new code, you'll get this result:

?FixCdnPostalCode("R3T3R81@")

R3T3R81@ is invalid.

Regular Expression purists among you might wonder why I'm using the SubMatches collection, rather than using the $1, $2 notation. This was the only way I could find to trim off extra characters from the end when I did get the "trim off the end" feature working (I warned you, I'm not an expert on Regular Expressions). Because I didn't add a reference to the regular expression, I used this declaration:

Dim reCurr As Object

You might prefer to set a reference so that you use early binding and this declaration:

Dim reCurr As RegExp

This declaration gives you IntelliSense when you're working in your code. You can add a reference to the regular expression library through VBA code:

Function AddRegExpReference() As Boolean

On Error GoTo Err_AddRegExpReference

Dim booStatus As Boolean

Dim refCurr As Reference

Dim strFile As String

  booStatus = True

  strFile = "C:\WINNT\SYSTEM32\vbscript.dll\3"

  Set refCurr = References.AddFromFile(strFile)

End_AddRegExpReference:

  AddRegExpReference = booStatus

  Exit Function

Err_AddRegExpReference:

  booStatus = False

  Err.Raise Err.Number, _

      "AddRegExpReference", _

      Err.Description

  Resume End_AddRegExpReference

End Function

Obviously, you'll need to change the code so that the path name to vbscript.dll in the code points to wherever the file is on your machine. You can't set a reference through the Access GUI because of that "\3" at the end of the filename. You can also add the reference by referring to the library's GUID. As far as I know, this code should work, regardless of what version of VBScript is installed on your machine, but I can't guarantee that:

Function AddRegExpReferenceGUID() As Boolean

On Error GoTo Err_AddRegExpReferenceGUID

Dim booStatus As Boolean

Dim refCurr As Reference

Dim strGUID As String

  booStatus = True

  strGUID = "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}"

  Set refCurr = References.AddFromGuid(strGUID, 5, 5)

End_AddRegExpReferenceGUID:

  AddRegExpReferenceGUID = booStatus

  Exit Function

Err_AddRegExpReferenceGUID:

  booStatus = False

  Err.Raise Err.Number, _

      "AddRegExpReferenceGUID", _

      Err.Description

  Resume End_AddRegExpReferenceGUID

End Function

That's about all I'm going to say about Regular Expressions, even though we've barely scratched the surface (there are even more neat things you can use them for). Hopefully this has been enough to spark your interest into looking into Regular Expressions a bit more (and, maybe, now you can hold your head up high when your Perl-speaking friends scoff at you because Access isn't as robust for text handling).

 

Your download file is called  310steele.ZIP in the file SA2003-10down.zip

This can be purchased with all the other downloads on this page