Tutorial: Access parsing expressions

Parsing Access data is a frequent task. If the data's inconsistent, find an anchor and work your way through.

In Tutorial: Parsing in Excel, I showed you a few expressions for parsing inconsistent name entries. The logic of relying on the position of specific characters is just as useful in Access, although Access doesn't use the same functions.

The Access table below stores names in firstname lastname format in a single field named Name. Some, but not all entries have middle initials. Using the following expression, extracting the first name is fairly simple:

FirstName: Left([Name],InStr([Name]," ")-1)
The InStr() function returns the position of the first space character. Consequently, the Left() function extracts characters from the beginning of the entry, up to the first space character. Omit the -1 component if you need to include the space character.

Extracting the last name takes just a bit more work:

LastName: Right([Name],Len([Name])-InStrRev([Name]," "))

This expression applies the same logic, plus some. The length of the entire name minus the position of the last character returns the name of characters to extract, beginning with the last character. Using Robin Banks, this expression evaluates as follows:

Right("Robin Banks",11-6)
Right("Robin Banks",5)

As you might suspect by now, extracting the middle initial takes even more work:

MI: IIf(InStrRev([Name]," ")>InStr([Name]," "),Mid([Name],InStr([Name]," ")+1,InStr([Name]," ")-2),"")

The IIf() function compares the position of the first space character and the second space character. If they're the same, there's only one space character and consequently, no middle initial (and I could've written the condition that way, just as easily). If the position of the last space character is greater than the position of the first space character, there's a middle initial (or something!) between the first and last names. The Mid() function then uses the position of the first space character to extract two characters between the first and last names. Those two characters, in this case, are the middle initial and the period character following each initial. If some names have a period character and some don't, this expression will return inconsistent results. Using Dan D. Lyons, this expression evaluates as follows:

IIf(7>4,Mid("Dan D. Lyons",4+1,4-2)," ")
IIf(True,Mid("Dan D. Lyons",5,2)," ")
Mid("Dan D. Lyons",5,2)

When parsing inconsistent data, you have to find some kind of anchor. In this example, the anchor is the position of the space characters. It's important to note that the " " component in all of the expressions is not an empty string. There's a literal space character between the two quotation marks.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox