Manipulating names

[Ricky_Mamba]

I have a query with either a single name or joint names separated by a comma in one field:

Examples:

Mr Mike Bloggs
Mr Fred Smith, Mrs Jean Smith
Mr Richard Jones, Mrs Sally Sugar
Mrs Alice Springs, Mr John Springs

Using the above examples, I need a new field as follows:

Mr M Bloggs
Mr & Mrs F & J Smith
Mr & Mrs R & S Jones & Sugar (as different surnames)
Mr & Mrs J & A Springs (ideally, in this example, where there is Mrs & Mr, the Mr & associated initial comes first).

Thanks

 

[Dhathaway]

If your entries will always have exactly two words in their name - no middle initials, no "Mary Ann Whatevers" - then you can use the Instr() function in a VBA module to look for spaces (" "), and then for the letter or word immediately following those spaces.

It will be a little bit of work, which is why most databases store first and last names in separate fields.

Let me know if you have trouble.

 

[Ricky_Mamba]

Thanks for your reply, but I'm struggling a bit, as have never written a VBA module before. Any further help would be appreciated.