Try this again, query. 1

[Skins64]

I have a field called "Dear" which contains "Mr Smith" "Ms. Jones" etc....This field also contains preferred names like Bob or Ted or Buck.

I need to update a field called "Preferred Name" with contacts who have a Bob or Buck etc in the "Dear" field.

Do not want to update preferred name with names that begin with Mr. or Ms.

Hopes this makes sense.


I guess I need to write a string that says...when "Dear" DOES NOT contain Mr. Mrs. Ms. Dr. etc update "Preferred Name" field.

[Preferred Name] = Dear
Where Left([Dear],3) NOT IN ('Mr.', 'Mr ', 'Mrs', 'Ms ', 'Dr.')

The above does not work in my query, any suggstions?

Thanks!

 

[PHV]

In the SQL view pane of the query window:
UPDATE yourTableNameHere
SET [Preferred Name] = [Dear]
WHERE Left([Dear],3) Not In ('Mr.','Mr ','Mrs','Ms ','Dr.')


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Skins64]

Thanks...works great!