Handle Apostrophes in SQL Parameter

Just passing by and saw this, no time to test right now, but what if you enclose the value in double quotes (which have to be doubled up inside a string), like this:

SQL = "TRANSFORM Round(Sum(tblDepletions_DETAIL.detQuan),0) AS Sales"
SQL = SQL + " SELECT tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal"
SQL = SQL + " FROM (tblDistributors INNER JOIN tblDepletions ON tblDistributors.distDistID = tblDepletions.depDistID) INNER JOIN (tblProducts_DEPL INNER JOIN tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID"
SQL = SQL + " WHERE (((tblDistributors.distParentCo)=""[forms]![frmRPT_COMP_PntDepl_SF]![cmbDist]"") AND ((tblDepletions.depDate) Between [forms]![frmRPT_COMP_PntDepl_SF]![BegRollDT] And [forms]![frmRPT_COMP_PntDepl_SF]![EndRollDT]))"
SQL = SQL + " GROUP BY tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal ORDER BY tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal"
SQL = SQL + " PIVOT DateDiff("m",[forms]![frmRPT_COMP_PntDepl_SF]![BegRollDT]-1,[depDate]) In (1,2,3,4,5,6,7,8,9,10,11,12);"

Select allOpen in new window

---

»bp

Thank you Bill and Joe for your replies!

I applied your suggestions in more pared down select SQL statements, below.  While I know this is the correct approach, neither of the ways I applied your solutions worked.

To test, removing the criteria, line #4, returns all records.

#1

SQL = "SELECT tblDepletions_DETAIL.detID, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal, tblDistributors.distParentCo, tblDepletions.depDate"
SQL = SQL + " FROM (tblDistributors INNER JOIN tblDepletions ON tblDistributors.distDistID = tblDepletions.depDistID) INNER JOIN (tblProducts_DEPL INNER JOIN tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID"
SQL = SQL + " GROUP BY tblDepletions_DETAIL.detID, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal, tblDistributors.distParentCo, tblDepletions.depDate"
SQL = SQL + " HAVING (((tblDistributors.distParentCo)=Replace("D'Angelo",Chr(39),"")))"
SQL = SQL + " ORDER BY tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal;"

Select allOpen in new window

#2

SQL = "SELECT tblDepletions_DETAIL.detID, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal, tblDistributors.distParentCo, tblDepletions.depDate"
SQL = SQL + " FROM (tblDistributors INNER JOIN tblDepletions ON tblDistributors.distDistID = tblDepletions.depDistID) INNER JOIN (tblProducts_DEPL INNER JOIN tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID"
SQL = SQL + " GROUP BY tblDepletions_DETAIL.detID, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal, tblDistributors.distParentCo, tblDepletions.depDate"
SQL = SQL + " HAVING (((tblDistributors.distParentCo)=Replace("D'Angelo","'","''")))"
SQL = SQL + " ORDER BY tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal;"

Select allOpen in new window

You need to double up double quotes when you want them inside a string like you do, so in #1 try:

SQL = SQL + " HAVING (((tblDistributors.distParentCo)=Replace(""D'Angelo"",Chr(39),"""")))"

In my previous example, I pasted the test code (using the criteria VALUE) instead of the form reference, containing the value.
Using the criteria VALUE: "D'Angelo" - just like that works!
It's about getting this to run w/ the form reference: [forms]![frmRPT_COMP_PntDepl_SF]![cmbDist]

SQL = "SELECT tblDepletions_DETAIL.detID, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal, tblDistributors.distParentCo, tblDepletions.depDate"
SQL = SQL + " FROM (tblDistributors INNER JOIN tblDepletions ON tblDistributors.distDistID = tblDepletions.depDistID) INNER JOIN (tblProducts_DEPL INNER JOIN tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID"
SQL = SQL + " GROUP BY tblDepletions_DETAIL.detID, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal, tblDistributors.distParentCo, tblDepletions.depDate"
SQL = SQL + " HAVING (((tblDistributors.distParentCo)=Replace([forms]![frmRPT_COMP_PntDepl_SF]![cmbDist],Chr(39),"")))"
SQL = SQL + " ORDER BY tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal;"

Select allOpen in new window

I have to step out.  Will return in a few hours.  Thank you, all, for your thoughts.  ~  Jacob

Instead of

SQL = SQL + " FROM (

SQL = SQL & " FROM (

Ampersand is the concatenation symbol in Access ... in all places

Thank you, All!  Everyone's comments have been helpful.
Joe, thank you for: SQL = SQL & " FROM (  : I was attempting to make the SQL of this query more readable and not thinking.  I appreciate your syntax reminder.

To back up a little + simplifying this query:

SELECT tblDistributors.distParentCo, tblDepletions.depID, tblDepletions.depDate
FROM tblDistributors INNER JOIN tblDepletions ON tblDistributors.distDistID = tblDepletions.depDistID
GROUP BY tblDistributors.distParentCo, tblDepletions.depID, tblDepletions.depDate
HAVING (((tblDistributors.distParentCo)=Replace([forms]![frmRPT_COMP_PntDepl_SF]![cmbDist],Chr(39),"")));

Select allOpen in new window

Both:

Replace([forms]![frmRPT_COMP_PntDepl_SF]![cmbDist],Chr(39),"")

Select allOpen in new window

And:

Replace([forms]![frmRPT_COMP_PntDepl_SF]![cmbDist],"'","''")

Select allOpen in new window

Return data with all text strings (names), as long as the field value of tblDistributors.distParentCo, DOES NOT contain an apostrophe.
When the field value of tblDistributors.distParentCo DOES contain an apostrophe, no data is returned.

--------
When the form field reference is replaced with: D'Angelo

SELECT tblDistributors.distParentCo, tblDepletions.depID, tblDepletions.depDate
FROM tblDistributors INNER JOIN tblDepletions ON tblDistributors.distDistID = tblDepletions.depDistID
GROUP BY tblDistributors.distParentCo, tblDepletions.depID, tblDepletions.depDate
HAVING (((tblDistributors.distParentCo)="D'Angelo"));

Select allOpen in new window

Data IS returned.

Backing up + simplifying even further,

SELECT tblDistributors.distParentCo
FROM tblDistributors
WHERE (((tblDistributors.distParentCo)=[forms]![frmRPT_COMP_PntDepl_SF]![cmbDist]));

Select allOpen in new window

works EVEN WITH a name containing an apostrophe.
'Must be something else about this query.

Backing up further to the original crosstab query:

TRANSFORM Round(Sum(tblDepletions_DETAIL.detQuan),0) AS Sales
SELECT tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal
FROM (tblDistributors INNER JOIN tblDepletions ON tblDistributors.distDistID = tblDepletions.depDistID) INNER JOIN (tblProducts_DEPL INNER JOIN tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID
WHERE (((tblDistributors.distParentCo)=Replace([forms]![frmRPT_COMP_PntDepl_SF]![cmbDist],Chr(39),"")) AND ((tblDepletions.depDate) Between [forms]![frmRPT_COMP_PntDepl_SF]![BegRollDT] And [forms]![frmRPT_COMP_PntDepl_SF]![EndRollDT]))
GROUP BY tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal
ORDER BY tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal
PIVOT DateDiff("m",[forms]![frmRPT_COMP_PntDepl_SF]![BegRollDT]-1,[depDate]) In (1,2,3,4,5,6,7,8,9,10,11,12);

Select allOpen in new window

WHERE (((tblDistributors.distParentCo)=Replace([forms]![frmRPT_COMP_PntDepl_SF]![cmbDist],Chr(39),""))

Select allOpen in new window

Returns data ONLY when names in tblDistributors.distParentCo contain no apostrophes.......

Okay, so as I mentioned, the SELECT works with the apostrophes withour any REPLACE etc.  Good.

Now you just have to figure out what else in that TRANSFORM query isn't performing as you expect.  Without the form and database it's pretty hard to do testing here though, but you seem to be digging into it.

---

»bp

Alrightee, and the answer is replacing apostrophes in BOTH the field value AND selection criteria!
Replacing apostrophes in ONLY the selection criteria will never match a field value containing an apostrophe!.
Actually, Joe, your solution was the answer! I just needed to apply it in BOTH references in the WHERE clause.

WHERE ((Replace([distParentCo],Chr(39),""))=Replace([forms]![frmRPT_COMP_PntDepl_SF]![cmbDist],Chr(39),""))

Select allOpen in new window

Correctly working crosstab query:

TRANSFORM Round(Sum(tblDepletions_DETAIL.detQuan),0) AS Sales
SELECT tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal
FROM (tblDistributors INNER JOIN tblDepletions ON tblDistributors.distDistID = tblDepletions.depDistID) INNER JOIN (tblProducts_DEPL INNER JOIN tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID
WHERE (((Replace([distParentCo],Chr(39),""))=Replace([forms]![frmRPT_COMP_PntDepl_SF]![cmbDist],Chr(39),"")) AND ((tblDepletions.depDate) Between [forms]![frmRPT_COMP_PntDepl_SF]![BegRollDT] And [forms]![frmRPT_COMP_PntDepl_SF]![EndRollDT]))
GROUP BY tblDistributors.distParentCo, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal
ORDER BY tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblDepletions_DETAIL.detFormat, tblProducts_DEPL.DprodVarietal
PIVOT DateDiff("m",[forms]![frmRPT_COMP_PntDepl_SF]![BegRollDT]-1,[depDate]) In (1,2,3,4,5,6,7,8,9,10,11,12);

Select allOpen in new window

Thank you ALL for your assistance here!  I appreciate your patience and perseverance.
Your suggestions and questions invited me out of narrow vision into a more broad perspective, which lead to the solution.
And Gustav, your function is great!  I will definitely keep it in mind for future applications.

With Gratitude, Jacob

Cool. !
I use Chr(34) double quote and Chr(39)  single quote because multiple single/double quotes are hard to read.
For example ... w/o pasting into VBA ... just looking at it there ... how many double/single quotes do you see here:
'""''"

right :-)