first and last name button

[bricklebrit]

Hello,

I am redoing some of my main data and seperating the Customer information into first and last name and then street address in seperate fields.

Right now, I have the field [CustomerName], I'm trying to build a button that, when clicked, will automatically put the first and last name into the corresponding fields:

[CustomerFirstName]
[CustomerLastName]

In a report in the past, I've used the stock code:
Trim(Left([CustomerName],InStr(1,[CustomerName]," ")-1

and

Trim(Right([CustomerName],InStr(1,[CustomerName]," ")-2

but I am having difficulty with the syntax in programming a button to assign these values to new field values.

Thanks in advance for any assitance or advice!

 

[Rich]

Use a query to separate the fields, once your satisfied it's returning the results you want change it to an append query.

 

[bricklebrit]

Rich -

Thanks for your prompt response. I am unfamiliar with being able to program queries with a statement like:

Trim(Left([CustomerName],InStr(1,[CustomerName]," ")-1

Do I do this in the SQL view? Could you give me a starting point?

Thanks in advance.

 

[David R]

Your query will have two fields, one to extract the last name, one to extract the first name. If the two words are merely separated by a comma, no spaces, you should be able to use Left() and InStr for the last name, and Right()/InStr() for the first name. You'll have to do a little math if there's spaces involved, but you seem to have the grasp of it.

Then once you're satisfied with the results, run it as an Update Query and put those two boxes into your new fields. Check your data one more time, then delete the old field.

Good luck,
David R


[This message has been edited by David R (edited 04-09-2002).]