Find and replace help: Smith/jones to Smith/Jones

[snipe]

Hey guys,

Little help if you don't mind. Making a mail merge list and the last names for unmarried couple have been pulled out of the database as "Smith/jones." This looks terrible on a letter (the lowercase of the second name). I have tried the following, and it works great in the immediate window, but when I put it into the query, it converts everything to uppercase. Any ideas?

Replace("Smith/jones","/" & Mid("Smith/jones", InStr("Smith/jones","/")+1,1),StrConv(Mid("Smith/jones",InStr("Smith/jones","/"),2),1)) 

When I put in the actually field name ([SHIP_TO_LNAME]) in place of "Smith/jones," it just puts everything to upper case.

[snipe]

Actually, it puts all of the names with a "/" to upper case, if a name doesn't have a "/," ie "Jones" it errors out.

Now I'm really stumped.

[ItsMe]

I have the following in a general module to adjust the first letter in a field to capital. I just CALL "NoCaps". The part you are looking for is vbProperCase within StrConv

Public Function NoCaps(strText As String) As String
NoCaps = StrConv(strText, vbProperCase)
End Function

[snipe]

But "Smith/jones" is 1 word. That doesn't capitalize the "j," tried that.

[ItsMe]

I am not familiar with your tables. For me, I usually have first and last names in separate columns SOMEWHERE. So if I import data, I put it into tables in a way I can index it, assign Keys, sort, etc.

I suppose you are going to need to use the forward slash to get first and last separated. Use the character as a delimiter so to speak. You could do the whole thing logically or you could place the separated data into a table for safe keeping.

[snipe]

Like I said, these are just the last names. First names are already separated. If Wendy Smith and Bob Jones bought something, it got keyed as FNAME=WENDY/BOB, LNAME=SMITH/JONES. The first names have been converted to Wendy & Bob, but the last names are to stay "slashed" but I want the 2nd last name to be capitalized.

Obviously I could use 3 queries to break them apart, capitalize, then put them back together. I am just trying to avoid that.

[ItsMe]

Like I said, these are just the last names. First names are already separated. If Wendy Smith and Bob Jones bought something, it got keyed as FNAME=WENDY/BOB, LNAME=SMITH/JONES. The first names have been converted to Wendy & Bob, but the last names are to stay "slashed" but I want the 2nd last name to be capitalized.

Obviously I could use 3 queries to break them apart, capitalize, then put them back together. I am just trying to avoid that.

I see. This one is obviously above my pay grade.

[snipe]

Well I appreciate you taking a look at it

[June7]

Might require a custom VBA function to handle because of the inconsistency in presence of /.

Call function from the update query just like calling intrinsic function. Here is a simple example, assumes only one /.

Put function in a general code module.

Function FixCap(strName As String) As String
Dim aryNames As Variant
FixCap = strName
If InStr(strName, "/") > 0 Then
aryNames = Split(strName, "/")
FixCap = aryNames(0) & "/" & StrConv(aryNames(1), vbProperCase)
End If
End Function

Probably insignificant but this means last names that would not customarily be capitalized will be. Vincent Willem van Gogh is one. Seems the van in Dutch names is often not capitalized.

[snipe]

Perfect, June7. I had to add a StrConv to aryNames(0) as all of the data is originally in all caps. Works perfect now. Thanks a bunch!