By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,200 Members | 1,612 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,200 IT Pros & Developers. It's quick & easy.

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

P: 11
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?
Expand|Select|Wrap|Line Numbers
  1. Function ParseFirstComp(pValue) As String
  2. Dim LPosition As Integer
  3. 'Find postion of space
  4. LPosition = InStr(pValue, " ")
  5. 'Return the portion of the string before the space
  6. If LPosition > 0 Then
  7. ParseFirstComp = Left(pValue, LPosition - 1)
  8. Else
  9. ParseFirstComp = ""
  10. End If
  11. End Function
  12.  
  13. Function ParseSecondComp(pValue) As String
  14. Dim LPosition As Integer
  15. 'Find postion of space
  16. LPosition = InStr(pValue, "_")
  17. 'Return the portion of the string after the space
  18. If LPosition > 0 Then
  19. ParseSecondComp = Mid(pValue, LPosition + 1)
  20. Else
  21. ParseSecondComp = ""
  22. End If
  23. 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
May 13 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 636
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
May 14 '08 #2

NeoPa
Expert Mod 15k+
P: 31,766
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.
May 14 '08 #3

P: 11
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.
Jun 9 '08 #4

NeoPa
Expert Mod 15k+
P: 31,766
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.
Jun 10 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.