Splitting up a text field by last space into two fields

[SherryW]

Hello;

I am looking for a very simple function that would allow me to split up a
name field into ie: first/middle name and last name.

The name field is always as follows: Homer J Simpson. I would like to
create a first name last name filed such as First Name: Homer J Last
Name: Simpson.

Is there a function out there that would allow me to do this within the
query design mode?

 

[Rick Brandt]

Hello;

I am looking for a very simple function that would allow me to split up a
name field into ie: first/middle name and last name.

The name field is always as follows: Homer J Simpson. I would like to
create a first name last name filed such as First Name: Homer J Last
Name: Simpson.

Is there a function out there that would allow me to do this within the
query design mode?

FirstName: Left(FieldName, InStr(1,FieldName," ")+1)
LastName: Mid(FieldName, InStr(1, FieldName, " ")+3)

 

[SherryW]

Thanks Rick, you're a genius.
--
SherryW

FirstName: Left(FieldName, InStr(1,FieldName," ")+1)
LastName: Mid(FieldName, InStr(1, FieldName, " ")+3)

 

[SherryW]

Thanks Allen, that works perfectly as well.

You've saved me a load of time.
--
SherryW

Depending on you version of Access, you might be able to use InstrRev() to
find the last space, and Mid() to pick up the remainder of the string.

Of, if you prefer a complete function, see:
ParseWord()
at:
http://allenbrowne.com/func-10.html
Use -1 for the last word, e.g.:
ParseWord([FullName], -1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello;

I am looking for a very simple function that would allow me to split up a
name field into ie: first/middle name and last name.

The name field is always as follows: Homer J Simpson. I would like to
create a first name last name filed such as First Name: Homer J Last
Name: Simpson.

Is there a function out there that would allow me to do this within the
query design mode?

 

[Allen Browne]

Depending on you version of Access, you might be able to use InstrRev() to
find the last space, and Mid() to pick up the remainder of the string.

Of, if you prefer a complete function, see:
ParseWord()
at:
http://allenbrowne.com/func-10.html
Use -1 for the last word, e.g.:
ParseWord([FullName], -1)