Splitting Name Field

[abbaddon223]

Hi,

I have a field: [CustomerName] which contains:

Mr Chris Smith

As an example. In some cases there is a "." after the Title, IE:

Mr. Chris Smith.

Ideally I want to be able to split these into

Title
Forename
Surname

But mainly just get the forename.

Can anyone help please?

 

[Beetle]

Assuming that there is always a space between each portion of the name, then in a query you could use an expression like the following;

FirstName: Mid([CustomerName],InStr([CustomerName]," "),InStrRev([CustomerName]," ")-InStr([CustomerName]," "))

If you want to be able to extract all three portions of the name you can use the Split function using the space as the delimiter. This will create an array that you can then loop through to process the individual portions of the name. This would be something that you would do in code, not directly in a query.

 

[abbaddon223]

Hi there,

Thanks for replying.

So would I use your query in the criteria field of a select query?

Thanks again for helping me!!

 

[jdraw]

Depending on how many names/records you have, and if this is a recurring need, you may wish to add fields to your Table.
Set up query along logic of beetle's post and update your records.
You can also use the info in your Title/Salutation to put some discipline on the Mr. ,Mr ,Mrs., Mrs, Ms, Ms., Miss...... issue you're likely to face.
Also, when everything is "dumped into one field" you are likely to have space, multiple space, no space, periods or not, initials or not.... sometimes it's better to print out the field and get a quick "eyeball" of what's really there and adjust your fix-up logic accordingly.