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