Access - Last Name and First Name fields - characters before space

Please offer why the following does not work for removing a Last Name field of any characters after the first space, going from left to right:

Left([Last Name],InStr([Last Name]," ")-1)

and this does not work for the First Name field as well, with Last Name replaced by First Name.

My understanding is that for Last Name, we begin from the left, go to the space, and then move back one to get all the characters prior to the space.

Last Name and First Name are two different fields.

Hope this makes sense.
exp vgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
are you really using the correct field [Last Name] in the expression?

the Left() function you posted is correct
0
PatHartmanCommented:
If they are two different fields, why are you trying to parse them?  [Last Name] should only contain the last name and [First Name] should only create the first name.
0
exp vgAuthor Commented:
Let me clarify - I change the Last Name to First Name in the formula. So, the table has a Last Name field and a First Name field - both starting from left to right.

When I run the query - the fields go blank.

Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
I don't understand your explanation.  Please post your code and give us an example of the input and the output and tell us why it isn't what you expected.
0
Rey Obrero (Capricorn1)Commented:
@exp vg

open the table and see the name of the field containing the "FULL NAME"  i.e., DOE JOHN

use the name of that field in your Left() expression,
if the name of the field is [NAME]

Left([Name],InStr([Name]," ")-1)
0
exp vgAuthor Commented:
Another clarification please, this does not have one fullname field, just two separate last and first name fields.

Will this formula work for just one of the name fields at a time - first and then last? Logically it seems that it should - as I am also trying to understand the logic in the statement.

Thank you.
0
Rey Obrero (Capricorn1)Commented:
What are the contents of fields [Last Name], [First Name] ?
.. post sample values here

if the content is not in the format  "DOE JOHN", you will not get wht you  are expecting to get.

in short the format must be "DOE JOHN" to get the  last name and first name.

is it clear?
0
PatHartmanCommented:
I'll try once more.  If the [Last Name] field ONLY contains last name, what are you trying to remove with the formula?  

If you don't post your query and sample data, we won't be able to help.
0
exp vgAuthor Commented:
Trying again - and thank you in advance for your help.

Column-First Name
Cary Ann
Beth
Elizabeth Mary
Beth J.

After query
Cary
Beth
Elizabeth
Beth

Hope this helps
0
exp vgAuthor Commented:
This may also help - I keep getting an error message indicating that the records could not be updated due to a type conversion error.

I then run the query and all the first name fields go blank.

Thank you.
0
exp vgAuthor Commented:
If this is too much trouble do not worry about it - we can get to this later. I am just not sure how to award the points.

Thank you.
0
Rey Obrero (Capricorn1)Commented:
try this..

 IIF(InStr([First Name]," ")>0,   Left([First Name],InStr([First Name]," ")-1), [First Name])


you are getting error for values that does not have a SPACE, i.e.,  Beth
0
exp vgAuthor Commented:
And the last name is in the format:

Column B - Last Name
Williams Shaw
W. Montgomery
Johnson


Should all become
Shaw
Montgomery
Johnson

Hope this makes sense.

In all my research - I did not see a code that was similar to the one you offered for First Name.

Here is my attempt at Last Name

IIF(InStr([First Name]," ")>0,   Left([First Name],InStr([First Name]," ")-1), [First Name])

IIF(InStr([Last Name]," ")>0, Right([Last Name],Instr([Last Name]," ")+2), [Last Name])

Unfortunately my Last Name formula did not work.

Thanks.
0
Rey Obrero (Capricorn1)Commented:
<Unfortunately my Last Name formula did not work.>
you need to make sure that your data are clean, meaning NO leading or trailing spaces

I normally use Mid() instead of the Right() function


IIF(InStr([Last Name]," ")>0, Mid([Last Name],Instr([Last Name]," ")+1), [Last Name])
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.