Solved Get count of field when source includes an apostrophe (1 Viewer)

dmgg

Registered User.
Local time
Today, 07:43
Joined
Apr 11, 2014
Messages
19
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.
 

plog

Banishment Pending
Local time
Today, 09:43
Joined
May 11, 2011
Messages
11,613
You need to do a Replace:

CountLN = DCount("[lname]", "[T_Customer]", "lname = '" & Replace([LN],"'","''") & "'")

In the DCount you are delimiting whatever is in LN by placing a single quote around it so it knows its a string value. But that apostrophe/single quote in the name makes it thinks its reached the end of the input value and then its got all these characters after it. So Replace will work around it.

Double quotes in [LN] might cause an issue as well.
 

dmgg

Registered User.
Local time
Today, 07:43
Joined
Apr 11, 2014
Messages
19
THANKS SO MUCH This was driving me nuts. Can't thank you enough! I looked all over the web.
 

Mike Krailo

Well-known member
Local time
Today, 10:43
Joined
Mar 28, 2020
Messages
1,032
Am I missing something here. Couldn't you just do a Totals query grouping on the LName field of your Customer table and populate the name count that way.

Code:
SELECT Customer.LName, Count(Customer.LName) AS CountOfLName
FROM Customer
GROUP BY Customer.LName;

This works as well if you had to stuff the last name into the critera of the query from a form:
Code:
SELECT Customer.LName, Count(Customer.LName) AS CountOfLName
FROM Customer
GROUP BY Customer.LName
HAVING (((Customer.LName)=[forms]![Customer].[form]![LN]));
 
Last edited:

dmgg

Registered User.
Local time
Today, 07:43
Joined
Apr 11, 2014
Messages
19
Thanks for reply. I got Gasman's reply installed and it is working. I will give this one a try tomorrow. Thanks again.
 

Users who are viewing this thread

Top Bottom