Creating a salutation field from a name field in Access 2007 - replacing characters (1 Viewer)

[KateM]

Hello - I hope someone can help.

We have an Access 2007 database with many thousands of names and addresses, for a mailshot. The name field is a single field with the title, initials and surname in the one field. We need to create salutation fields using the title and surname only.

The added complication is that the names are different lengths.

E.g.

Ms J Lewis
Mrs P D G Jones-Green

So far I've tried using replace, I've tried trim too. Using Right([name],5) as an example only works for names of the appropriate length and I cannot find an easy way to categorise the lengths of name to run the query several times.

Is there code I can use that would select everything to the right of the last space ?

I'm sure lots of people must have to do this....

Please advise, thank you.

Kate

 

[pbaldy]

Try the Left() function combined with InStr() to find the position of the first space, and the Mid() function combined with InStrRev() to find the position of the last space.

 

[arnelgp]

create a function in a module that will do it for you.
then include that function in your query:

public function fnSalutation(v as variant) as variant
dim s() as string
dim i as integer
dim sRet as string
s = split(s, " ")
for i = lbound(s)+1 to ubound(s)-1
sRet = sRet & UCase(left(s(i),1)) & " "
next i
fnSalutation=Trim(strconv(s(0),vbProperCase) & " " & sRet & strConv(s(ubound(s)), vbProperCase))
end function

to include in your query:

Field: Salutation: fnSalutation([FieldName])

 

[KateM]

Hi PBaldy

Thanks for your quick reply. I'd never come across InstrRev before. I read that the syntax is different for InStrRev but I cannot work out why I cannot get any results from

InstrRev([Name]," ") with criteria >"0"

On my test database every name should have a value for this field.

Please forgive my ignorance on this.... !

Thanks

Kate

 

[jdraw]

Kate,

I think arnelgp's function will do most of what you want. Great start!

I made up some test data, and made 1 adjustment to his function.

Here's the test data and result.

Note: Surnames with an embedded quote eg O'Reilly will be O'reilly. You could program around that with more code/logic.

The change to the function was s = Split(v, " ")

Test routine

Sub testSalParse()
          Dim mns(3) As String
          Dim i As Integer
10       On Error GoTo testSalParse_Error

20        mns(0) = "Mrs Patricia Donna Geraldine Jones-Green"
30        mns(1) = "Ms Ali vant Goor"
40        mns(2) = "Oscar de la Hoya"
50        mns(3) = "Ms Patty O'Reilly"
60        For i = 0 To UBound(mns)
70            Debug.Print fnSalutation(mns(i))
80        Next i

90       On Error GoTo 0
100      Exit Sub

testSalParse_Error:

110       MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure testSalParse"
End Sub

Result from test:

Mrs P D G Jones-green
Ms A V Goor
Oscar D L Hoya
Ms P O'reilly

Good luck with your project. Thanks arnelgp.