I've seen many posts here about how to handle names with an apostrophe in them, but have come up with a slightly different quandary: How do I remove those pesky apostrophe's from a name? MY boss said he doesn't want to waste time coding for the apostrophes, that he'd rather remove the apostrophe's.

If anyone has a suggestion that might help my headache be a little smaller, I would love it. Thanks in advance!!

Lookup the Replace() function

Example:
strString = Replace(strString,"'","")

MY boss said he doesn't want to waste time coding for the apostrophes, that he'd rather remove the apostrophe's.

There is no extra coding. Rather than using the following:

strSQL = strSQL & "WHERE MyField = 'Smith'"

You just substitute each delimiting ' with "".

i.e.

strSQL = strSQL & "WHERE MyField = ""Smith"""

Thank you all for your fast replies....that is wonderful! And appreciated. I believe I was close to making a mountain out of a molehill. Wouldn't it be wonderful if Microsoft would incorporate some of this forums ideas and suggestions also? :D

It's good you're happy with the delimiter change because people who have single quotes in their names might object to having them removed:)

Hi
I had a similar issue (An apostrophe makes the DLookUp function crash and burn) and I stripped the apostrophes out. This looks like a much better way of handling apostrophes, but I have a question.

If you are referring to a control on a form, rather than hard-coding the 'Smith' part, would you code it as follows?

strSQL = strSQL & "WHERE MyField = """ & Forms!MyForm.MyField & """"
TIA, Andrew

Yes, that's right. :)

Pat, I agree.... actually, this "plight" of mine was geared toward a parts processing system and so had nothing to do with people and their names. I just have that old funny feeling that the boss will later on ask that the apostrophes be put bck in and won't that be fun!!!! Actually, I'm backing up all data before stripping out the apostrophes, so am covered already.

And again, to all who replied, thank you so much! This forum is a ton easier to understand than Microsoft. :)