first/last conversion

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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.
 
Back
Top