Clean up old table field

[MediaDoc]

Hi,

Working on an pre-existing database. Original structure has a field in a table called "Salutation" which is what they want to appear at the top of letters etc.

It contains info like

Mr. Berger
Ms. Johnstone
Executive Director
Sarah

I want to clean up this field, and have created a combo box that lets them select the most common Salutations (Mr. Mrs. Dr. etc.) or add their own, which works for new clients.

I want to create a query (or something) that when it finds an entry in this old field like Mr. XXXX or Mrs. XXXX it removes the last part of the data (i.e. no sense storing thier last name in 2 fields).

Not sure how to go about something like this.

Thanks!

Matts

 

[RichMorrison]

Matt,

I have done this kind of thing in the past. It always worked best to make a successive series of tables. Each table would contain the modified content of the previous. If something gets messed up, you can go back and resume from the last good version.

First you should use Trim$ to remove leading and trailing spaces. Second you would make a Select query that uses the InStr function to find a period in the field. Third, use a combination with Left$ and Len to remove the leading title: Mr. Ms. Dr. etc.

You want to eyeball each change to see if anything strange happened.

HTH,
RichM