Need help parsing "name" field into fname, lname, middleinit

[KrazyKasper]

I'm a Novice User using Access 2003
Tables are via ODBC (i.e., cannot alter fields)

I have a report that uses the field OrderRepName (text string) and is formatted as lastname, firstname, middleinitial (No prefixes or suffixes, etc.). Sometimes the field is blank and sometimes there is no middle initial.

I found the following code on the Internet and it seems like it will work except I get an error message regarding Null values, and it doesn't seem to handle middle initials. (I modified it slightly replacing the word "underscore " with "space").

Can someone help me modify the following code, or suggest something else?

Function ParseFirstComp(pValue) As String
Dim LPosition As Integer
'Find postion of space
LPosition = InStr(pValue, " ")
'Return the portion of the string before the space
If LPosition > 0 Then
ParseFirstComp = Left(pValue, LPosition - 1)
Else
ParseFirstComp = ""
End If
End Function
 
Function ParseSecondComp(pValue) As String
Dim LPosition As Integer
'Find postion of space
LPosition = InStr(pValue, "_")
'Return the portion of the string after the space
If LPosition > 0 Then
ParseSecondComp = Mid(pValue, LPosition + 1)
Else
ParseSecondComp = ""
End If
End Function

This first function called ParseFirstComp will return the portion of the string before the space. The second function called ParseSecondComp will return the portion of the string after the space.
Next, you'll need to use this function in your query

(picture - but it won't let me paste it into this posting)

In the example above, we've used both functions to parse a field called Name. You will need to substitute your field name with the ParseFirstComp and ParseSecondComp functions. So we've typed ParseFirstComp([Name]) in the first field and ParseSecondComp ([Name]) in the second field. Access assigns the field name of "Expr1" and "Expr2" - you can always overwrite this.

Now, when we run the query, we'll get the following results:

(Picture - but it won't let me paste it into this posting)

Thanks,

Krazy

[MikeTheBike]

I'm a Novice User using Access 2003
Tables are via ODBC (i.e., cannot alter fields)

I have a report that uses the field OrderRepName (text string) and is formatted as lastname, firstname, middleinitial (No prefixes or suffixes, etc.). Sometimes the field is blank and sometimes there is no middle initial.

I found the following code on the Internet and it seems like it will work except I get an error message regarding Null values, and it doesn't seem to handle middle initials. (I modified it slightly replacing the word "underscore " with "space").

Can someone help me modify the following code, or suggest something else?

Function ParseFirstComp( pValue) As String
Dim LPosition As Integer
'Find postion of space
LPosition = InStr(pValue, " ")
'Return the portion of the string before the space
If LPosition > 0 Then
ParseFirstComp = Left(pValue, LPosition - 1)
Else
ParseFirstComp = ""
End If
End Function

Function ParseSecondComp (pValue) As String
Dim LPosition As Integer
'Find postion of space
LPosition = InStr(pValue, "_")
'Return the portion of the string after the space
If LPosition > 0 Then
ParseSecondComp = Mid(pValue, LPosition + 1)
Else
ParseSecondComp = ""
End If
End Function


This first function called ParseFirstComp will return the portion of the string before the space. The second function called ParseSecondComp will return the portion of the string after the space.
Next, you'll need to use this function in your query

(picture - but it won't let me paste it into this posting)

In the example above, we've used both functions to parse a field called Name. You will need to substitute your field name with the ParseFirstComp and ParseSecondComp functions. So we've typed ParseFirstComp([Name]) in the first field and ParseSecondComp ([Name]) in the second field. Access assigns the field name of "Expr1" and "Expr2" - you can always overwrite this.

Now, when we run the query, we'll get the following results:

(Picture - but it won't let me paste it into this posting)

Thanks,

Krazy

Hi

Just a thought, have you tried using the string functions directly in the SQL, ie

SELECT Left([Name],InStr([Name]," ")-1) AS FName, Mid([Name],InStr([Name]," ")+1) AS SName
FROM tblTechnicians;

or won't the ODBC driver alow this.

Queries work faster with Native function than with bespoke function witten by the programmer (if you have a significant number of records)!!

Note: If the above works, and you have Initials (or not) between the first and last name then try this

SELECT Left([Name],InStr([Name]," ")-1) AS FName, Mid([Name],InStrRev([Name]," ")+1) AS SName
FROM tblTechnicians;

InStRev() searches from the end backwords.

HTH


MTB

[NeoPa]

I've never seen commas (,) after a first name and before an initial.

Can you explain precisely and accurately the layout of the name you are working with and what possible alternatives can be found (Which parts are mandatory and which optional)?

I presume you want to take this single field and populate three fields; FName, LName and MiddleInit.

[KrazyKasper]

I've got it now.
Thanks to all for your help on this problem.

Krazy

p.s. If this thread needs to be closed, someone please let me know how to do that.

[NeoPa]

No worries Krazy. We just like a solution posted where possible that's all. We tend not to close or lock threads as somebody else may want to add a helpful comment later.