Link to home
Create AccountLog in
Avatar of Chi Is Current
Chi Is CurrentFlag for United States of America

asked on

Handle Apostrophes in SQL Parameter

The following query works fine, as long as there are no apostrophes in field value: tblDistributors.distParentCo  --  ex: "D'Angelo"
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);"

Open in new window


I tried adding a 'Replace' function in Line #4, which does not work.  Error: This expression is typed incorrectly, or is too complex to be evaluated...":
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)=Replace([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);"

Open in new window

'Would appreciate your ideas for a solution!

Many Thanks! ~ Jacob

P.S.  Stepping away for a bit.
Avatar of Bill Prew
Bill Prew

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);"

Open in new window



»bp
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Chi Is Current

ASKER

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;"

Open 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;"

Open 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),"""")))"
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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;"

Open 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
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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),"")));

Open in new window

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

Open in new window

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

Open 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"));

Open in new window

Data IS returned.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Backing up + simplifying even further,
SELECT tblDistributors.distParentCo
FROM tblDistributors
WHERE (((tblDistributors.distParentCo)=[forms]![frmRPT_COMP_PntDepl_SF]![cmbDist]));

Open 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);

Open in new window

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

Open 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),""))

Open 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);

Open 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 :-)