The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

A Little Logic Goes a Long Way – Parsing to Pull Things Apart, Part 2


  1. Why can't a man living in the United States be buried in Canada?
  2. Is it legal for a man in California to marry his widow's sister? Why?
  3. Why do Chinese men eat more rice than Japanese men do? 


  1. Because he is alive.
  2. No, it’s not legal to marry someone if you’re dead.
  3. There are more Chinese men than Japanese men.

These three questions are minor tests of logic. Although a bit simple, they require the need to think clearly and, sometimes, out of the box.
Last month, we looked at parsing a series of names, including the name I.D. Clair. Our logic was based on the existence of spaces. Using that logic, the first name was I.D. and the last name was Clair.
We know that the letters I and D represent initials and that I.D. is not a first name. By using a little bit more logic, we can make our code more sophisticated so it can distinguish between initials and names.

A Refresher on our Logic

In last month’s issue, if you recall, we separated names into the first name, middle name (if there was one), and last name. You can see the entire code and thought process in the September 2012 issue.
One of the problems we had was handling the occurrence of two initials and a name, as in I.D. Clair. If we use just a little bit of logic we can solve our problem. Consider the following set of names.
I.D. Clair
Chris P. Bacon
Dan D. Lyons
Earl E. Bird
I.M. Spartacus

Using our space logic from last month, we would have no problems with Cris P. Bacon, Dan D. Lyons, and Earl E. Bird. However, our logic fails on I.D. Clair and I.M. Spartacus – it improperly decides that the leading initials are the first name. Our problem is that whoever typed these names into the computer neglected to put a space after the period of each initial. This could have been intentional or accidental; however in any case, we want to our logic to be able to handle this type of situation.

The Solution and New Code

By applying just a touch of logic and one line of code, we can solve our problem very easily. If we just add a space after every period, the logic will fly just fine.
Since one of the steps that we use at the end of the process to eliminate extraneous spaces is to trim the value that we returned from the function, the extra spaces will disappear with no further work on our part.
Using this method, I.D. Clair becomes I. D. Clair and I.M. Spartacus, becomes I. M. Spartacus.
In our code, we had a parameter that was passed in called strName. Toward the beginning of the code, we add the line
     strName = Replace (strName, “.”, “. ” )
And that’s all we have to do!

The Integrated Code and Conclusion

Function ParseName(strName As String, strFirstMiddleOrLast As String) As String
    Dim intFirstSpace As Integer
    Dim intLastSpace As Integer
    Dim intLenName As Integer
    Dim rv As String
    strName = Trim(strName)
    strName = Replace (strName, “.”, “. ” )
    strName = Replace(strName, "  ", " ")
    intLenName = Len(strName)
    intFirstSpace = InStr(1, strName, " ")
    intLastSpace = InStrRev(strName, " ")
    Select Case strFirstMiddleOrLast
        Case Is = "First"
            rv = Left(strName, intFirstSpace)
        Case Is = "Last"
            rv = Right(strName, intLenName - intLastSpace)
        Case Is = "Middle"
            rv = Mid(strName, intFirstSpace, intLastSpace - intFirstSpace) & ""
         Case Else
            rv = "unexpected Request for Name to Return - use First, Middle or Last Name"
    End Select
    rv = Trim(rv)
    ParseName = rv
End Function
Notice that immediately following our new line that adds a space after any initial…
    strName = Replace (strName, “.”, “. ” )
… we have the line that removes doubled spaces.
    strName = Replace(strName, "  ", " ")
So at the end of the process, we have dealt with a very tricky problem with a bit of logic and some truly trivial code. Just as in tackling the logic problems presented at the beginning of this newsletter, if we take a moment to think and look outside the box, sometimes we can come up with a very elegant solution that is almost trivial to implement. 


The next time you are faced with a problem and have the urge to jump right into a solution, take a moment to reflect. Just as we solved our problem of distinguishing between names and initials, you can sometimes come up with a simple answer to what appears to be a complicated problem.

An Easy Way to Copy a Value from the Prior Record

When you are in a continuous form and need to repeat the value from the last record, you can do it with a neat keyboard shortcut. Put your cursor in the same field that you want to copy from the prior record, then just hold down the control key and hit the apostrophe key.
This trick will also work in a single record form. It is not as obvious because you can’t see the prior record, but it will still work and it can save a lot of typing.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886