first/last conversion

[Guest]

I have a field that for a number of reasons needs to be entered as FirstName
LastName in access. I have a second field which is the field that I use to
sort. Is there a way to automatically take the first field data, convert to a
lastname, firstname format, from which I can stick into that sort field.

Usually, this would be quite easy with macros in excel, but I'm at a bit of
a loss in access. Is this easily doable?

Would appreciate any help, thanks ever so much!

 

[Steve Schapel]

Billy,

Is there any reason why you can't put FirstName and LastName in two
separate fields. This is the "correct" approach.

Anyway, to answer your question, this is not a job for a macro. It is a
job for a calculated field in a query...
SortName: Mid([YourNameField],InStr([YourNameField]," ")+1) & ", " &
Left([YourNameField],InStr([YourNameField]," ")-1)

But this won't be perfect. Especially in the case of anyone with a
2-word FirstName such as "Billie Jo McAllister" will result in "Jo
McAllister, Billie". So, as mentioned, a proper database design is the
best idea.