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