Data Count interfered with apostrophe

[gg80]

When enter a last name in a form, it generates a count of the last name in the source table. The count is used to display a list of same names if count >1. It works fine except if a name has an apostrophe (like O'Briant)

Here is the code that works fine with no apostrophe included. LN is field where enter last name.
CountLN = DCount("[lname]", "[T_Customer]", "lname = '" & [LN] & "'")



When last name includes a space or dash, no problem

When the name includes an apostrophe, get this message:
Syntax error (Missing Operator) in query expression "Lname=O'Briant"

Any help much much appreciated.

[Welshgasman]

Use Replace() and replace ' for two of the single quotes, and you should be OK?

[Micron]

Another way (because I don't grasp the first suggestion but that's probably on me. Seems like the suggestion is to count O'Brien as O''Brien which shouldn't work?)

DCount("[lname]", "[T_Customer]", "[lname]= " & Chr(34) & [LN] & Chr(34))

[orange]

The procedure of doubling up on the single quote is called escaping the quote (or at least it used to be).
Just replace a single quote with 2 single quotes and all will be well.

[Welshgasman]

Cross posted even after responses here
https://www.access-programmers.co.uk...trophe.318152/

[gg80]

Thanks much. Works and simpler than another reply that I got. Thanks again.

[gg80]

I think that I answered the wrong message. Your way works just fine. Simpler than the one I got from another site. Thanks very much.

I can't figure out how to mark this as solved. Will do as soon as I can.

[gg80]

I replid to wrong solution. You are right, I got the reply from the English site and worked well. They responded before This site.

[Bob Fitz]

I replid to wrong solution. You are right, I got the reply from the English site and worked well. They responded before This site.

Thanks for informing everyone who tried to help you, that they were wasting their time. I'm sure they had nothing better to do. I would advise you to read the following link about the etiquette of cross-posting: https://www.excelguru.ca/content.php?184