removing 'Mr' from cell containing 'Mr Robert' 2

[greenfibres]

Hello
I have an Access table with separate fields for Title Firstname and Surname.
Unfortunately many of the Firstname cells include a Title, so the Title is empty and the Firstname reads Mr Robert and the Surname reads Burns.
How can I remove the Mr from the Firstname and place it in the Title?

I hope there's a simple solution out there!
All the best
Jim

 

[lespaul]

Are they all Mr or are some Mr.?

Are there others: Ms, Ms., Mrs and Mrs.? What about Dr.?

Is there always a space between the end of the title and the beginning of the firstname?

I would start with a SELECT query to see if I'm getting all the right data:

SELECT Left(NameField, inStr(NameField, " ")) As Title, Right(inStr(NameField, " ") + 1, NameField) As JustName, NameField As OriginalField FROM tableName

If that looks good then:

UPDATE TableName SET Title = Left(NameField, inStr(NameField, " ")), NameField = Left(inStr(NameField, " ") + 1, NameField)

be sure to make a backup before running ANY update query!

Good luck


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[greenfibres]

Hi leslie
Thanks for the reply
1. I tried the select query but the JustName field comes out as an error.

Also the Title field will take the first initial from a multiple initial name like J R Hartley, so I ended up using the Where function

This is the code I finished up with, but as I said, the JustName field comes out as an error

Almost there!!

Thanks,
Jim

PS. I really am a beginner with this stuff, so if I've made a glaring error translating your advice, please show me!

SELECT Left([ContactFirstName],InStr([ContactFirstName]," ")) AS Title, Right(InStr([ContactFirstName]," ")+1,[ContactFirstName]) AS JustName, Customers.ContactFirstName AS OriginalField
FROM Customers
WHERE (((Customers.ContactFirstName) Like "miss *" Or (Customers.ContactFirstName) Like "ms *" Or (Customers.ContactFirstName) Like "ms. *" Or (Customers.ContactFirstName) Like "mrs *" Or (Customers.ContactFirstName) Like "mrs.*" Or (Customers.ContactFirstName) Like "mr *" Or (Customers.ContactFirstName) Like "mr. *" Or (Customers.ContactFirstName) Like "dr *" Or (Customers.ContactFirstName) Like "dr. *" Or (Customers.ContactFirstName) Like "prof *" Or (Customers.ContactFirstName) Like "prof. *"))
ORDER BY Customers.ContactFirstName;

 

[PHV]

Some misuses of the function.
Try this:
SELECT Left([ContactFirstName],InStr([ContactFirstName]," ")-1) AS Title, Mid([ContactFirstName],InStr([ContactFirstName]," ")+1) AS JustName
FROM ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[greenfibres]

Hi PH
Unfortunately I just get the message
'The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.'

Can you check that again?
Thanks
Jim

 

[PHV]

What is YOUR actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[greenfibres]

Hi PH

Here's the code

SELECT Left([ContactFirstName],InStr([ContactFirstName]," ")-1) AS Title, Mid([ContactFirstName],InStr([ContactFirstName]," ")+1) AS JustName FROM Customers WHERE (((Customers.ContactFirstName) Like "miss *" Or (Customers.ContactFirstName) Like "ms *" Or (Customers.ContactFirstName) Like "ms. *" Or (Customers.ContactFirstName) Like "mrs *" Or (Customers.ContactFirstName) Like "mrs.*" Or (Customers.ContactFirstName) Like "mr *" Or (Customers.ContactFirstName) Like "mr. *" Or (Customers.ContactFirstName) Like "dr *" Or (Customers.ContactFirstName) Like "dr. *" Or (Customers.ContactFirstName) Like "prof *" Or (Customers.ContactFirstName) Like "prof. *"))
ORDER BY Customers.ContactFirstName;

 

[greenfibres]

Hello again

Strangely, when I go back to the query as suggested by Leslie, and then cut and paste PH's suggestion in, it works!
Thanks PH.

The only thing to fix is when in the ContactFirstName the only entry is Mr. or Mrs. and nothing else - then I get an error in the Title field.

Here's the code

SELECT Left([ContactFirstName],InStr([ContactFirstName]," ")-1) AS Title, Mid([ContactFirstName],InStr([ContactFirstName]," ")+1) AS JustName
FROM Customers...