Validate input such as ZIP codes and postal codes

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:

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:

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

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:

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:

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

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:

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

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:

Here are some samples of what this function can do:

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:

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:

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

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:

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:

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   Steele_Validate_ZIPcodes.accdb


About Doug Steele

Doug Steele has worked with databases-both mainframe and PC-for many years with a major international oil company. In a previous life, he taught introductory computer programming at the University of Waterloo. Microsoft has recognized him as an Access MVP for his contributions to the user community over the years.

Doug is the author of Access Solutions with Arvin Meyer.

This entry was posted in Access Controls. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.