Reversing name order

  • Thread starter Thread starter W. Guy Delaney
  • Start date Start date
W

W. Guy Delaney

In my database, I have the names of the parents of my children entered with
the last name first followed by their first name. For instance: Johnson,
Calvin & Dorothy

In some instances, I need to have the names in reverse order, such as Calvin
& Dorothy Johnson.

How can I enter a formula in my query to accomplish this reversal.
 
In my database, I have the names of the parents of my children entered with
the last name first followed by their first name. For instance: Johnson,
Calvin & Dorothy

In some instances, I need to have the names in reverse order, such as Calvin
& Dorothy Johnson.

How can I enter a formula in my query to accomplish this reversal.
Click to expand...

The need to do this (and the need to be able to search efficiently on
lastnames, firstnames or both) is why most developers prefer to store
surnames in one field, and given names in another; it's easier to put
them together using a calculated field like

FullName: [LastName] & ", " & [FirstName]

or

FullName: [FirstName] & " " & [LastName]

However, given your structure, you can use a rather snarky string
expression:

Trim(Mid([namefield], InStr([namefield], ",") + 1) & " " &
Left([namefield], InStr([namefield], ",") - 1)

A question: given that it's the 21st century, what do you plan to do
if the child's parents are "Mark Jones and Jane Smith"? Not all
parents marry, and of those that do, not all women take their
husband's last name!
 
Back
Top