exp vg
asked on
Access - One Name Field with Spaces
Please offer how I can separate out a name field that has the following in one field:
first name, middle initial/name (may not always be included, last name - in one field
Therefore
JOHN S SMITH
JOHN SAM SMITH
JOHN SMITH
All become:
JOHN SMITH
The last name formula works, but the first name does not:
Last Name: Mid([Name First/Last],InStrRev([Name First/Last]," ")+1) - works
First Name: Left([Patient First/Last],InStr([Patient First/Last]," ")-1) - does not work
Thank you.
first name, middle initial/name (may not always be included, last name - in one field
Therefore
JOHN S SMITH
JOHN SAM SMITH
JOHN SMITH
All become:
JOHN SMITH
The last name formula works, but the first name does not:
Last Name: Mid([Name First/Last],InStrRev([Name
First Name: Left([Patient First/Last],InStr([Patient
Thank you.
ASKER
I can also do this in Excel - with some manual manipulation - but it is appreciated if I can do this in Access .
Thank you.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
ASKER
Example
JOHN SAM WILLIAMS SMITH
will still become
JOHN SMITH
This name field is always separated by a space.
Thank you.