Access 2002 Programming by Example

Using Functions for Pattern Matching in Queries

Custom functions can do more than fulfill your wish list. They can fill in the gaps of features that Access forgot. Sometimes, problem solving requires you to seek a solution that Access doesn’t offer in its standard list of features.

Suppose you had a table that you had obtained from an outside source that had a list of names that you wanted to parse into first, middle, and last name fields. First you would need to find out which records had only first and last names as opposed to first, middle, and last. You could use the Like "* *" wildcard pattern for the criterion in a query to pick up first and last names only. The only problem is that this wildcard pattern will also pick up first, middle, ...