trim

[Guest]

i have a ton of names that someone put in a table like the following example

smith, john
jones, dave

i want to put the last name in one field and first name in another field. is
there anyway that i can do this with a trim function or something. to say
give me all data to the left of the comma and give me all data to right of
comma?

 

[fredg]

i have a ton of names that someone put in a table like the following example

smith, john
jones, dave

i want to put the last name in one field and first name in another field. is
there anyway that i can do this with a trim function or something. to say
give me all data to the left of the comma and give me all data to right of
comma?

All of the data to the left of the first comma:
Lastname:Left([FullName],InStr([FullName],",")-1)

All Data to the right of the first comma:
[FirstName]:Mid([FullName],InStr([FullName],",") + 2)

You might want to look up what a function does (i.e. Trim) in VBA help
before suggesting it as a possible solution.
While you're there, look up Left, Right, Mid, and InStr as well.

 

[kingston via AccessMonster.com]

If all fields are as you described (Last Name, First Name):

Last Name = Mid([Field],1,Instr([Field],",")-1)
First Name = Mid([Field],Instr([Field],",")+2)

You can use the Trim() function if you want to.

 

[Jeff Boyce]

In Access, the Trim() command removes leading and trailing spaces, so you
probably don't want that one.

The Left() and Mid() and Instr() functions can be used to parse apart a
full-name string like you have.

However, (and this is a big but) Access is extremely literal when it applies
these three functions. If you are 100% confident that your FullName field
only contains "LastName, FirstName", push on ahead with these functions.

If, perchance, your FullName field includes things like:

Cher
van der Wigge, John
Jones, Jr., Jim

then your use of those three functions might break.

You may find that you can use some variation of these functions to get a
start, but the final conversion requires USB - using someone's brain!

Good luck!

Jeff Boyce
Microsoft Office/Access MVP