need to change text around

[MES via AccessMonster.com]

Is there a way to change the order of the text in a field? For example, I
have a name field in my table that has last name,first name (ex. - Doe,John).
I need to change the order of the text in that field to read first name last
name (ex. - John Doe).

Thanks in advance for any help here.

 

[Rick B]

I would suggest you normalize your data. You are storing two pieces of data
in one table. You need a FirstName field and a LastName field, instead of
your combined name field. Then, you can display the name as "last, first"
or "first last" or "Dear First:" or "Mr. Last", etc.

 

[Guest]

How about splitting the first and last names into separate fields in your
database. Then you can join (concatenate) them any way you please. This is
also much easier to search, sort, etc....

If they are all separated by a comma, you could add a computed column to a
query that looks like:

Name_First_Last: Mid([NameField], instr([NameField], ",") + 1) & " " &
Left([NameField], instr([NameField], ",") -1)

To prevent an error from occuring on those records that don't have a comma,
you will need to do something like:

Name_First_Last: iif(instr([NameField], ",") = 0, [NameField],
Mid([NameField], instr([NameField], ",") + 1) & " " & Left([NameField],
instr([NameField], ",") -1))
Criteria: instr([NameField], ",")

HTH
Dale