Change "Allie/shawn" to "Allie & Shawn"

[snipe]

Hey guys,



I have a table (pic shown) of a customer list, obviously with most data not shown for privacy reasons.

Our sales get keyed in in all caps. Also, when a couple purchases, their name is keyed as FNAME1/FNAME2.

I have a query series that finds each customer that has spent $5000+, puts their names and address into Sentence Case, and then exports to word for a mail merge for mailings that I sign and send out.

For couples, the letters look like crap with the slash combined with what sentence case does to it.

How could I find all records with a "/" and replace it with " & " and turn make the first letter of the second name a capital?

This gets done once a month, and it is on all new data. A UDF would be fine, almost preferred.

[pbaldy]

You can use the Replace() function in an update query to actually change the data, if that's what you're after.

[snipe]

Ah got it! Used Len() and InStr() to separate them and made a UDF to capitalize the first letter.

EDIT: Investigating the Replace() function, but marking as solved. Appreciate it!

[Bob Fitz]

You could change the second name to start with a capital Using the StrConv() function.

[pbaldy]

The Replace() function would be far more efficient, though it wouldn't handle the capitalization. I would think you could do that with the StrConv() function, like StrConv(Replace(...)) with all the proper arguments of course.

[snipe]

Thanks for the replace idea..

StrConv(Replace("Bob/barbara","/"," & "),3)

Stupid amounts of simple.

[pbaldy]

No problem.