asked on

Handle Apostrophes in VBA SQL - Part 2

Friends ~ The following statement works fine, AS LONG AS there are no apostrophes in field: tblDistributors.distParentCo

    strSQL1 = "SELECT tblDepletions_Detail.detZSKU, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblProducts_DEPL.DprodVarietal, tblDepletions_Detail.detFormat"
    strSQL1 = strSQL1 & " 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"
    strSQL1 = strSQL1 & " WHERE (((tblDistributors.distParentCo) ='" & [Forms]![frmRPT_YTD_pntDepl_SF]![cmbDist] & "') And ((tblDepletions.depDate)Between #" & [Forms]![frmRPT_YTD_pntDepl_SF]![BDT1] & "# And #" & [Forms]![frmRPT_YTD_pntDepl_SF]![EDT2] & "#))"
    strSQL1 = strSQL1 & " GROUP BY tblDepletions_Detail.detZSKU, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblProducts_DEPL.DprodVarietal, tblDepletions_Detail.detFormat;"

Open in new window

I have tried replacing apostrophes in field: tblDistributors.distParentCo - Results in Error #3075 (Missing operator)

    strSQL1 = "SELECT tblDepletions_Detail.detZSKU, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblProducts_DEPL.DprodVarietal, tblDepletions_Detail.detFormat"
    strSQL1 = strSQL1 & " 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"
    strSQL1 = strSQL1 & " WHERE (((Replace[tblDistributors].[distParentCo],Chr(39),Chr(39)Chr(39)))= Replace('" & [Forms]![frmRPT_YTD_pntDepl_SF]![cmbDist] & "',Chr(39),Chr(39)Chr(39))) And ((tblDepletions.depDate)Between #" & [Forms]![frmRPT_YTD_pntDepl_SF]![BDT1] & "# And #" & [Forms]![frmRPT_YTD_pntDepl_SF]![EDT2] & "#))"
    strSQL1 = strSQL1 & " GROUP BY tblDepletions_Detail.detZSKU, tblProducts_DEPL.DprodBrand, tblProducts_DEPL.DprodLabel, tblProducts_DEPL.DprodVarietal, tblDepletions_Detail.detFormat;"

Open in new window

Clearly, Line #3 w/ 'Replace' function is incorrect.
Would be grateful for another take on syntax here.

Thank You, Jacob

[ fanpages ]

If I understand what you are trying to achieve; that being that you are trying to replace a single occurrence of Chr(39) with two Chr(39) characters, then the last parameter of the Replace(...) function needs to change from:

Chr(39)Chr(39)

to

Chr(39)&Chr(39)

That is:

strSQL1 = strSQL1 & " WHERE (((Replace[tblDistributors].[distParentCo],Chr(39),Chr(39)Chr(39)))= Replace('" & [Forms]![frmRPT_YTD_pntDepl_SF]![cmbDist] & "',Chr(39),Chr(39)&Chr(39))) And ((tblDepletions.depDate)Between #" & [Forms]![frmRPT_YTD_pntDepl_SF]![BDT1] & "# And #" & [Forms]![frmRPT_YTD_pntDepl_SF]![EDT2] & "#))"

Chi Is Current

ASKER

Thank you fanpages for your comment.

    strSQL1 = strSQL1 & " WHERE (((Replace[tblDistributors].[distParentCo],Chr(39),Chr(39)Chr(39)))= Replace('" & [Forms]![frmRPT_YTD_pntDepl_SF]![cmbDist] & "',Chr(39),Chr(39)&Chr(39))) And ((tblDepletions.depDate)Between #" & [Forms]![frmRPT_YTD_pntDepl_SF]![BDT1] & "# And #" & [Forms]![frmRPT_YTD_pntDepl_SF]![EDT2] & "#))"

Open in new window

Still results in Error #3075. Closer.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

[ fanpages ]

Still results in Error #3075. Closer.

:) I'm not surprised... I probably would not have used the approach you are attempting to incorporate within your SQL statement.

Do you specifically wish to strip single quote/apostrophe [ASCII Code 39] from the database column values, or would you actually like to store & retrieve the original value of the column (including this character)?

Chi Is Current

ASKER

Bill ~ This IS indeed MS Access SQL and your solution DOES indeed work with tblDistributors.distParentCo values containing apostrophes!

Though I do not understand why. How are the apostrophes in tblDistributors.distParentCo not in the way?

Bill Prew

I think I mentioned this in the "Part 1" post, but I apologize for not making it clearer.

MS Access can use either double quotes or single quotes around string literals in a SQL query. Whichever one you use in a query, the only character that will then be a problem if embeded in a text value is the type of quote you are using. So by using double quotes the single quotes that might occur between the double quotes are just treated like any other character. Maybe the examples below will be more clear...

***** VALID *****

WHERE Name = "O'Grady"
WHERE Name = 'O"Grady'

Open in new window

***** INVALID *****

WHERE Name = "O"Grady"
WHERE Name = 'O'Grady'

Open in new window

»bp

Chi Is Current

ASKER

Understood. Thank you, Bill. Best Regards, Jacob

[ fanpages ]

I guess my query will go unanswered then.

Bill Prew

Welcome, glad that was useful.

»bp

Chi Is Current

ASKER

fanpages: My apologies.

Do you specifically wish to strip single quote/apostrophe [ASCII Code 39] from the database column values, or would you actually like to store & retrieve the original value of the column (including this character)?

I was looking for a way to write a select query (in VBA) which handles apostrophes in criteria and tblDistributors.distParentCo field values, without changing stored values.

Best Regards, Jacob