Very often you must import data into an Access table and do some corrections to the data. The example in this article splits a column containing names (lastname,firstname) into separate columns for LastName and FirstName. It’s very easy to do these things with a small amount of VBA code and an Update Query.

I like to make Functions to accomplish these "data filtering" problems like separating the Last Name from the First Name, Uppercasing just the first letter of each word and so on. So I have a little tool kit of general purpose functions to do this routinely. But what I’ll present here is a greatly simplified version of three of these.

So to begin, I’ll show you the fictitious data we’re going to correct and also the end result. The idea here is that we start off with the column "Name" that isn’t adequate for our purposes and create two new columns "LastName" and "FirstName". Actually, "LastName" is the first word in the Name column (a ‘String" of text ) and "FirstName" is the rest of the "String". Notice that strings in the original column have commas separating the data, which makes it a little easier to accomplish, but we could have used just a blank or anything else.

So the first thing to do is to insert the columns we need. I simply inserted two new columns, "LastName" and "FirstName", and made them text. Then, referring back to Picture 1, I made a backup of the original data. In the Database Manager, simply right click on Members, and select Copy, then right click again and select Paste. Access selects "Structure and Data" for you, click OK on that and it will ask you for a new name. I selected "BackUp".

Now for a couple of VBA Functions. Open a new module, and start a new function:

Public Function ParseLastName(ByVal S As String) As String

End Function

This will be our first go at a function to pull the last name from the Name column. The S is the old, long Name string and it "returns" the last name. That’s what the "As String" at the end does. A Function takes zero or more parameters (in this case ByVal S as String) and returns a result. Notice that we want to do one database record at a time, and that suits this approach very well. Add to this:

Public Function ParseLastName(S As String) As String

' ************************************************************
' Returns all the characters in S that appear before the Comma
' Lowercase all, then Uppercase the first letter.
' ************************************************************

Dim I As Integer
I = InStr(1, S, ",", vbTextCompare)
If I < 2 Then
ParseLastName = ""
Exit Function
Else
S = Mid(S, 1, I - 1)
S = LCase(S)
Mid(S, 1, 1) = UCase(Mid(S, 1, 1))
ParseLastName = S
End If
End Function

The first four lines are comments (note the single quote at the beginning of the lines)

' ************************************************************
' Returns all the characters in S that appear before the Comma
‘ Lowercase all, then Uppercase the first letter.
' ************************************************************

Two of the lines are just decoration for readability, but the middle lines are a description of what the Function does. The line Dim I As Integer declares a variable that we’ll be using to save the position of the comma.

I = InStr(1, S, ",", vbTextCompare) uses the Instr(ing) function built into VBA. The Instr function uses three parameters: The position to start (1), The string to search (S) and the character string to look for—in this case, a comma. The vbTextCompare is an option that ignores case, so a lowercase "a" is the same as an uppercase "A". It doesn’t apply here because we’re looking for a comma, but we use it anyway. Instr saves the result in I. So this lines reads: Look for the comma in S, starting with the first character and save the result in I.

The line If I < 2 then is a test to see if the comma is not found (I will be a 0) or if it’s the first character (I = 1). If the comma is the first character, then obviously there is no last name, so we’ll just set the result to an empty string (the "") and exit the function. If I is 2 or more then there will be a last name of at least one character, so we’ll do the four lines between the Else and the End If.

S = Mid(S, 1, I –1) Uses the VB Mid function to extract the part of S from 1 to I – 1 and assigns it back to S.

S = LCase(S) Uses the LCase function to change the entire word to lower case.

Mid(S, 1, 1) = UCase(Mid(S, 1, 1)) Uses both the Mid function and the UCase function to change the first letter of the word to upper case.

ParseLastName = S Finishes it up, assigning the result to the function name ParseLastName before exiting the function.

The second function ParseRestName is almost identical to ParseLastName. Two changes were made. S=Mid(S, I + 1) is different from the previous function in that this one returns the part of S from I + 1 through to the end of the string – the remainder of the string past the comma. And we’ve added one line S=Trim(S). This removes any leading and trailing blanks from the string.

Public Function ParseRestName(ByVal S As String) As String
' **************************************************
' Returns all the characters in S that appear After the Comma
' Lowercase all, then Uppercase the first letter.
' ************************************************************

Dim I As Integer
I = InStr(1, S, ",", vbTextCompare)
If I < 2 Then
ParseRestName = ""
Exit Function
Else
S = Mid(S, I + 1)
S = LCase(S)
S = Trim(S)
Mid(S, 1, 1) = UCase(Mid(S, 1, 1))
ParseRestName = S
End If
End Function

So now we have the two functions for now. They look as shown in Picture 3.

In the Update to: row for LastName type in: ParseLastName([members].[name]), and for FirstName type in: ParseRestName([members].[name]). So this will use the ParseLastName function to update the LastName column and the ParseRestName to update the FirstName column. Close the query and name it SplitName, then run it. Here’s the query – Picture 4:

And when you run the query, here’s what the data will look like. (This picture is minus the Cline, P; I put this in later as a further test.)

Well, what’s wrong with this? Two things. One is that LaMartin is not correct, and the initials are not uppercased. So we’ll have to correct the initials. There just isn’t any way to correct LaMartin except manually. That’s OK here because we’ve saved a lot of work. Imagine if there were a thousand names.

Now to tackle the second problem, and we’ll write a new function UpperEachWord to Module1 for this.

Public Function UpperEachWord(ByVal S As String) As String

' **************************************************
' Lowercases all text and then Uppercases the first
' Letter of each word.
' **************************************************

Dim I As Integer
S = LCase(S) ' Lower case all characters
Mid(S, 1, 1) = UCase(Mid(S, 1, 1)) ' Always uppercase the first letter
For I = 1 To Len(S) - 1 ' Look at all characters except the last
If Mid((S), I, 1) = "." Or Mid((S), I, 1) = " " Or Mid((S), I, 1) = "," Or Mid((S), I, 1) = ";" Then
Mid(S, I + 1, 1) = UCase(Mid(S, I + 1, 1))
End If
Next I
UpperEachWord = S
End Function

And as the loop steps through each character of S, this group of statements tests the characters to see if the character is a blank, period, comma or semicolon. And if any are found, the combination of Mid and UCase will uppercase the Next character.

So we’ll close and save the module – it now has three functions. And we’ll make a new Update Query as before, and name it EachWord. It looks like this:

So in this update query, we’ll just update the FirstName column with an Update to: of UpperEachWord([members].[FirstName]). Save and run it. So the results now are:

Now to finish it up, you can select the Name column and hit the delete key. And save the module for the next time you have to transform names. I’ve probably converted a dozen or more tables in this manner.

It also points out the versatility of Visual Basic for Applications. VBA allows you to extend the language itself by writing your own functions and using them in the same manner as built-in functions. u