Reformatting a name field

[Tony Williams]

I have a field in a database that holds the name of individuals like this
"Williams, Tony" it is one field not made up of first name and surname
fields. Is there an expression which would enable me to show the data in this
field in a control on a report like "Tony Williams"
Thanks
Tony

 

[fredg]

I have a field in a database that holds the name of individuals like this
"Williams, Tony" it is one field not made up of first name and surname
fields. Is there an expression which would enable me to show the data in this
field in a control on a report like "Tony Williams"
Thanks
Tony

Using an unbound text control:
Mid([FullName],Instr([FullName],",")+2) & " " &
Left([FullName],InStr([FullName],",")-1)

The above should be all on one line.
Change [FullName] to whatever the actual name of the field is.

 

[John W. Vinson]

I have a field in a database that holds the name of individuals like this
"Williams, Tony" it is one field not made up of first name and surname
fields. Is there an expression which would enable me to show the data in this
field in a control on a report like "Tony Williams"
Thanks
Tony

I would strongly suggest changing the design of the table to contain both a
firstname and lastname field (and if you wish, fields for title, middlenames,
and suffix so you could handle "Williams, Dr. Tony James Robert Jr." if that
should be necessary. It's dead easy to concatenante multiple fields into one
display:

([Title] + " ") & LastName & (", " + [FirstName]) & (" " + [MiddleNames]) & ("
" + [Suffix])

for example - or [FirstName] & " " & [LastName] - or whatever is needed.

You can do so by adding the FirstName and LastName fields, and updating
LastName to

Left([yournamefield], InStr([yourfieldname], ",") - 1)

and FirstName to

Trim(Mid([yourfieldname], InStr([yourfieldname], ",") + 1)

Then search for records containing a blank in FirstName and fix manually (or
with some more complicated programming).

 

[Tony Williams]

Thanks Fred I'll try that.
Tony

I have a field in a database that holds the name of individuals like this
"Williams, Tony" it is one field not made up of first name and surname
fields. Is there an expression which would enable me to show the data in this
field in a control on a report like "Tony Williams"
Thanks
Tony

Using an unbound text control:
Mid([FullName],Instr([FullName],",")+2) & " " &
Left([FullName],InStr([FullName],",")-1)

The above should be all on one line.
Change [FullName] to whatever the actual name of the field is.

 

[Tony Williams]

Thanks John I agree with what you say, that's how I would normally do it, but
I have inherited this table from another database and need to find a way to
convert it either on a permannent basis or when I need to show the name in
reports. I'll be getting regular updates so didn't want to do anything too
complicated on the import.

Thanks for your help
Tony

I have a field in a database that holds the name of individuals like this
"Williams, Tony" it is one field not made up of first name and surname
fields. Is there an expression which would enable me to show the data in this
field in a control on a report like "Tony Williams"
Thanks
Tony

I would strongly suggest changing the design of the table to contain both a
firstname and lastname field (and if you wish, fields for title, middlenames,
and suffix so you could handle "Williams, Dr. Tony James Robert Jr." if that
should be necessary. It's dead easy to concatenante multiple fields into one
display:

([Title] + " ") & LastName & (", " + [FirstName]) & (" " + [MiddleNames]) & ("
" + [Suffix])

for example - or [FirstName] & " " & [LastName] - or whatever is needed.

You can do so by adding the FirstName and LastName fields, and updating
LastName to

Left([yournamefield], InStr([yourfieldname], ",") - 1)

and FirstName to

Trim(Mid([yourfieldname], InStr([yourfieldname], ",") + 1)

Then search for records containing a blank in FirstName and fix manually (or
with some more complicated programming).

 

[John W. Vinson]

Thanks John I agree with what you say, that's how I would normally do it, but
I have inherited this table from another database and need to find a way to
convert it either on a permannent basis or when I need to show the name in
reports. I'll be getting regular updates so didn't want to do anything too
complicated on the import.

What you may want to do then is go with a compromise. Have your table set up
with just FirstName and LastName fields, and - rather than importing from the
updates table - run an Append query, using the string parsing functions to
extract the first and last names.

 

[Tony Williams]

Thanks Brilliant!!!!!
Tony

What you may want to do then is go with a compromise. Have your table set up
with just FirstName and LastName fields, and - rather than importing from the
updates table - run an Append query, using the string parsing functions to
extract the first and last names.