Fore Name and Or surname

[DMT Dave]

Hi Guys, can anyone see the wood here ? i can't see the wood for the trees



If a text box has got Joe Bloggs in it, i am trying to return Joe

If a text box has only got Joe in it, i am still trying to return Joe

If a text box has got Mr Bloggs in it, i am trying to return Mr Bloggs (Not got this far yet to add code that if the text box says Mr or Mrs so needs adding to what i have got here below)

Dim FullName as string, fName as String, sName as string

FullName = InStr(Me.txtClientName, " ")
If FullName <> "" Then
fName = Left(Me.txtClientName, FullName) 'Does return Joe
End If
If FullName = "" Then
fName = Left(Me.txtClientName, "") 'RETURNS NOTHING
End If

[June7]

Name parts should be in separate fields. Trying to break them apart is a nightmare.

FullName is declared as a string but you set it to a number.

The second Left() function is not correctly used. Its second argument must be a number or an expression that returns a number, not an empty string. Why would you use Left() for that case anyway?

Need to test for Mr and Mrs but what about Ms, Dr, Senator and other courtesy titles?

[CJ_London]

And what about names like

Mary Anne Smith
George Huntingdon Smith

[DMT Dave]

ahh thank you very good point to replies you have sent

Would you be able to do a method in vba that would accommodate all ?

Kindest

[CJ_London]

Would you be able to do a method in vba that would accommodate all ?

all depends on the specifics - often requires a high level of customisation

[CJ_London]

the techniques used might be to use the following string functions, instr, instrrev, left, mid, right. In vba you might also use the split function to split your single field on space into multiple elements which you can loop through and examine and treat as required.

depends on whether this is a one off exercise to create the additional fields to go forward with or something you need to do on a regular basis.

One way or another you will almost certainly find fullnames which don't match the patterns you have already identified so best way to proceed is identify and fix the easy ones so you can focus on the more complex ones and improve your routines to cater for them.

You may find this link helpful

http://www.cpearson.com/Excel/FirstLast.htm

It's in excel, but the vba principle should still apply

[DMT Dave]

Thank you so much guys, i believe with so many IF cases or Selects cases then strings could be created but i will check out what you have suggested

Kindest