Split Full Name field

[Colleen]

I have basically the same question as Lisa except mine is in reverse. My
field is FirstName LastName. Can someone please help with how to code the
query to split the name field.

Thanks,
Colleen

 

[Allen Browne]

Grab the ParseWord() function from here:
http://allenbrowne.com/func-10.html

 

[Colleen]

Ok, I'm not an expert and I tried the parse in my query and it won't work. I
placed it in the field for LastName and it gave me an error - Undefined
function 'ParseWord' in expression. If that had worked I was going to try to
do the other example for the first name.
I have tried these but they do not work either.
Left([GuestName],InStr(1,[GuestName]," ")-1)
Right([GuestName],Len([GuestName])-InStrRev([GuestName]," "))

 

[Colleen]

I keep getting the same error with anything I try. It highlights the first
Paren before [Name] and states - The expression you entered has an invalid .
(dot) or ! operator or invalid parenthisis.
I am putting this code in my query under the field names I created in my
table to split the first and last name.

I've had some other issues with access and wonder if there is something
wrong with my software.

Thanks.

Hi,

Here is the code I developed to pull the firstname and lastname using a query.


For the firstname I used: FNAME: Mid$([Name],1,InStr(1,[Name]," ",1))

[NAME] would be your field that contains the FirstName LastName. FNAME would
be the field that is displayed that shows the first name.

For the lastname I used: LNAME: Mid$([Name],InStr(1,[Name]," ",1)+1,Len(
[Name])-InStr(1,[Name]," ",1)+1)

Again, [NAME] is your field and this field will display the last name.

This above assumes that their is a space, (" "), between the FirstName
LastName.

Hope this helps

I have basically the same question as Lisa except mine is in reverse. My
field is FirstName LastName. Can someone please help with how to code the
query to split the name field.

Thanks,
Colleen

 

[Colleen]

OMG, I'm an idiot.......... Forgot to make it an update query. Sometimes
I wonder where my brain is.

THANKS FOR YOUR HELP.........

Colleen

The code I showed you goes in the field: name of a select query. I would go
in the Criteria: name of an update query.

What type of query are you trying to use?

Hi,

Here is the code I developed to pull the firstname and lastname using a query.

For the firstname I used: FNAME: Mid$([Name],1,InStr(1,[Name]," ",1))

[NAME] would be your field that contains the FirstName LastName. FNAME would
be the field that is displayed that shows the first name.

For the lastname I used: LNAME: Mid$([Name],InStr(1,[Name]," ",1)+1,Len(
[Name])-InStr(1,[Name]," ",1)+1)

Again, [NAME] is your field and this field will display the last name.

This above assumes that their is a space, (" "), between the FirstName
LastName.

Hope this helps

I have basically the same question as Lisa except mine is in reverse. My
field is FirstName LastName. Can someone please help with how to code the
query to split the name field.

Thanks,
Colleen

 

[Allen Browne]

Ok, I'm not an expert and I tried the parse in my query and it won't work.

Seems like you have found another approach.
If there's no middle named (just one space), Instr() will do the job.