Formatting a name column using Text Functions

[JLKRK]

I am hoping this is fairly easy for some of you extremely smart folks. Here is my problem. A person originally create a "Name" column in the format "last name, first name". I would like to create two new columns, "First Name" and "Last Name". I am looking for a function to give me everything up to the comma "," (which would be the last name) and then everything after the comma "," (which would give me the first name). I would like to pull "Name" into a query and then create the "First Name" and "Last Name" columns in the query. Can somebody provide me with the basic function and associated code? Thanks, Jim

 

[dom_donald]

Hi,

the code is the following (at least one method!), using the "Surname, Forename" format:

Dim Name_Str As String
Dim Comma_Pos As Integer
Dim Surname As String
Dim Forename As String

Comma_Pos = InStr(1, Name_Str, ",", vbTextCompare)
Surname = Mid(Name_Str, 1, Comma_Pos - 1)
Forename = LTrim(Mid(Name_Str, Comma_Pos + 1, Len(Name_Str) - Comma_Pos))

you can do without the Comma_Pos line if you like, and combine it with the others:

Surname = Mid(Name_Str, 1, InStr(1, Name_Str, ",", vbTextCompare) - 1)
Forename = LTrim(Mid(Name_Str, InStr(1, Name_Str, ",", vbTextCompare) + 1, Len(Name_Str) - InStr(1, Name_Str, ",", vbTextCompare)))

but that looks a bit messy

Anyway, you can use these expressions in a query.

HTH
Dom

 

[JLKRK]

The problem is, if I am reading your code correctly, the comma isn't always in the same place. The length of last and first names are all different. I'm probably not understanding. I tried copying and pasting into the "builder" but it didn't seem to work.

 

[jfgambit]

Have a look at this sample...I believe it is what you need.

 

[JLKRK]

Thanks so much this is great! One problem though.....It worked beautifully to get the FirstName, but when I tried the LastName code it kept giving me an "Invalid procedure call" error. All I did was copy and paste your code and put in my column name, this is what I have......

LastName: Left([Borrower_Name],InStr([Borrower_Name],",")-1)

Any thoughts? Again the first name part works great!!!

Thanks, Jim

 

[DALeffler]

Is there a "," (comma) in every name string?

If not, "LastName: Left([Borrower_Name],InStr([Borrower_Name],",")-1)" will cause an error because "InStr([Borrower_Name],",")-1)" will return -1 and the "Left" function can't return -1 characters.


You can try this:

LastName: Iif(InStr([Borrower_Name],",") <> 0, Left([Borrower_Name],InStr([Borrower_Name],",")-1), [Borrower_Name])

If there is no comma in Borrower_Name, all of Borrower_Name will be in LastName.

 

[JLKRK]

Thanks so much!!

To JFGAMBIT and DOUG, thank you so much for your help!!! I always find this sight to be tremendously helpful and am so thankful that folks like yourselves take the time to help us hack/wanna be's!! I have my first and last names now! Thanks again, Jim