Query to remove middle initial from field?

[bgotis]

My table contains a field containing whole names (last, first, MI). Some of the names have middle initials, some don't. I am moving the data into a table where last name, first name and initial are all separate fields. I was able to separate the last name from the rest, but how do I separate a middle initial using a query?

TIA,
Mary

 

[raskew]

You should be able to adapt the following function to your needs. Try experimenting
with it from the debug window.

Function quickparse(texta As String, strSep As String)
'*******************************************
'Name: quickparse (Function)
'Purpose: Extracts word/expression for a string.
'Inputs: ? quickparse("Public, John, Q", ",")
'Output: Public
' John
' Q
'*******************************************

Dim texthold As String, textsay As String
Dim n As Integer
n = Len(texta)
texthold = texta
n = 0
Do While InStr(texthold, strSep) > 0
textsay = Left(texthold, InStr(texthold, strSep) - 1)
'add code to do something
Debug.Print textsay
texthold = Trim(Mid(texthold, InStr(texthold, strSep) + 1))
n = Len(texthold)
Loop
textsay = texthold
'add code to do something
Debug.Print textsay

End Function