Reverse names in one field

[AL1999 via AccessMonster.com]

Hi,

I was wondering if there is a way to reverse a name in a single field. Say
Ben Thomas to "Thomas, Ben" (without the quotes). I have tried one way:

- Right([contactname]),InStr(Len([contactname]))

But no luck. If anyone knows how to reverse a name, I would appreciate it.
Thank you.

Regards,

Al

 

[Guest]

You can parse on the space and then concatenate the parts.

Ah but there is a problem in using one field for names. Example as with a
double first name - Billy Joel Thomas -- Mary Ann Smith.

Right([contactname], Len([contactname]) - InStr([contactname]," ")) & ", " &
Left([contactname], - InStr([contactname]," "))

 

[Joseph Meehan]

Hi,

I was wondering if there is a way to reverse a name in a single
field. Say Ben Thomas to "Thomas, Ben" (without the quotes). I have
tried one way:

- Right([contactname]),InStr(Len([contactname]))

But no luck. If anyone knows how to reverse a name, I would
appreciate it. Thank you.

Regards,

Al

Will you always 100% have two names with a single space?

Of course the correct way is to divide the filed into first name and
last name fields.

 

[AL1999 via AccessMonster.com]

Hey Karl,

I tried your method, it gives me an error. I have also tried to tinker around
with it. Again, no luck.

Regards,

Al

You can parse on the space and then concatenate the parts.

Ah but there is a problem in using one field for names. Example as with a
double first name - Billy Joel Thomas -- Mary Ann Smith.

Right([contactname], Len([contactname]) - InStr([contactname]," ")) & ", " &
Left([contactname], - InStr([contactname]," "))

Hi,

[quoted text clipped - 9 lines]

Al

 

[fredg]

Hi,

I was wondering if there is a way to reverse a name in a single field. Say
Ben Thomas to "Thomas, Ben" (without the quotes). I have tried one way:

- Right([contactname]),InStr(Len([contactname]))

But no luck. If anyone knows how to reverse a name, I would appreciate it.
Thank you.

Regards,

Al

Well. I won't help you do what is bad Access programming (combining 2
names within 1 field), but I will help you separate the names into 2
separate fields (if your version of Access supports the InStrRev
function).

Add 2 fields to the table.
Run an Update query:

Update YourTable Set YourTable.LastName =
Mid([CombinedNames],InStrRev([CombinedNames]," ")+1),
YourTable.FirstNames =
Mid([CombinedNames],1,InStrRev([CombinedNames]," ")-1);

Once they are separated into 2 separate fields, it is a simple matter
to combine them as you wish in a query or in a report.
In a query:
FullName:[LastName] & ", " & [FirstNames]

or in a report:
=[LastName] & ", " & [FirstNames]

 

[Guest]

What kind of error?

Did you try half of it - just the last name? Just the first name?

Hey Karl,

I tried your method, it gives me an error. I have also tried to tinker around
with it. Again, no luck.

Regards,

Al

You can parse on the space and then concatenate the parts.

Ah but there is a problem in using one field for names. Example as with a
double first name - Billy Joel Thomas -- Mary Ann Smith.

Right([contactname], Len([contactname]) - InStr([contactname]," ")) & ", " &
Left([contactname], - InStr([contactname]," "))

Hi,

[quoted text clipped - 9 lines]

Al

 

[AL1999 via AccessMonster.com]

Hey Karl,

The error message says something, "the expression is typed incorrectly, or is
to complex to be evaluated...". I copied and pasted the formula you wrote.
But it keeps giving the above statement.

What kind of error?

Did you try half of it - just the last name? Just the first name?

Hey Karl,

[quoted text clipped - 18 lines]

 

[AL1999 via AccessMonster.com]

Hey Karl,

Yea it did work, there was a negative sign in the second Instr argument,
that's why it gave me an error.

Anyways, I appreciate your help and thank you.

Regards,

AL

You can parse on the space and then concatenate the parts.

Ah but there is a problem in using one field for names. Example as with a
double first name - Billy Joel Thomas -- Mary Ann Smith.

Right([contactname], Len([contactname]) - InStr([contactname]," ")) & ", " &
Left([contactname], - InStr([contactname]," "))

Hi,

[quoted text clipped - 9 lines]

Al