Reformatting a name field

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
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
Click to expand...

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.
 
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
Click to expand...

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).
 
Thanks Fred I'll try that.
Tony

fredg said:
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
Click to expand...

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.
Click to expand...
 
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

John W. Vinson said:
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
Click to expand...

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).
Click to expand...
 
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.
Click to expand...

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.
 
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.
Click to expand...

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.
 
Thanks Brilliant!!!!!
Tony

John W. Vinson said:
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.
Click to expand...
 
Thanks Brilliant!!!!!
Tony

John W. Vinson said:
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.
Click to expand...
 
Back
Top