troubleshooting Question

Handle Apostrophes in SQL Parameter

Avatar of Chi Is Current
Chi Is CurrentFlag for United States of America asked on
SQLMicrosoft AccessVBA
16 Comments4 Solutions316 ViewsLast Modified:
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);"

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);"
'Would appreciate your ideas for a solution!

Many Thanks! ~ Jacob

P.S.  Stepping away for a bit.
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Database Architect / Application Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2007

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 4 Answers and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros