Issues with strSQL

[gblack]

OK so I am trying to create a SQL string where the name of table is a variable. I can do this with no problem... my problem is that I am also trying to create a special field within to make a path...

So the path would look something like [Path] & [some_field] & "\" as New_Path...

The only problem is it doesn't work

I have used the ASCII Chr(34), chr(38) and even chr(92)...

But somehow, with extra double quotes, the singular ampersand (that need to be part of the SQL statment) and a back slash on the end (which needs to be in quotes within the statement)... VBA interprets any compbination of ASCII characters incorrectly... It either thinks I am trying to " & \ " or " & ""\"" "

I even tried using a string Variable to set it up... but no combination of double quotes and/or ASCII characters is working.

This code:

        strString = " " & Chr(38) & Chr(34) & Chr(92) & Chr(34) & " "
        
        strSQL = "SELECT ITEM.PARENT, [temp_" & strOneTOA_Name & "].[PATH] & [ITEM].[PARENT]" & strString & " AS PATH, " & _
                "[temp_" & strOneTOA_Name & "].LV, [ITEM].QUANTITY FROM temp_TOA_J04 INNER JOIN ITEM ON [temp_" & strOneTOA_Name & "].[CHILD] = ITEM.PARENT WHERE " & _
                "((([temp_" & strOneTOA_Name & "].[LV])= " & iLevel & " ));"

Comes out like so:
"SELECT ITEM.PARENT, [temp_TOA_J04].[PATH] & [ITEM].[PARENT] &""\"" AS PATH, [temp_TOA_J04].LV, [ITEM].QUANTITY FROM temp_TOA_J04 INNER JOIN ITEM ON [temp_TOA_J04].[CHILD] = ITEM.PARENT WHERE ((([temp_TOA_J04].[LV])= 1 ));"

Notice the extra double quotes around the backslash.

If you removed the Chr(34) then it comes out with no double quotes at all.

I think this combination just may not be doable...?

 

[WayneRyan]

G,

Add one more double-quote:

[ITEM].[PARENT] &"""\"" AS PATH

hth,
Wayne

 

[gblack]

Tried that.... still post:

[temp_TOA_A12].[PATH] & [ITEM].[PARENT] ""\"" AS PATH

what's up with the double double quotes?

 

[gblack]

Because I wan't a string value with an ampersand, and double quotes around a backslash... it was pretty much impossible. No matter what I tried, it either gave me a backslash without any quotes or a backslash with two sets of double quotes... I even tried using the Replace() function with chr(34)'s... to no avail.

What did end up working though was using a single quote (i.e., chr(39)) instead of chr(34). Hoepfully this will help someone else who's trying to build a path from a heirarchical query. Basically I was trying to mimick SYS_CONNECT_BY_PATH(parent, '/') in Oracle.

The single quotes did the trick...

 

[WayneRyan]

g,


Sorry it's been a while ...


I'd prefer to use SQL's single-quotes:

strSQL = "SELECT ITEM.PARENT, [temp_" & strOneTOA_Name & "].[PATH] & [ITEM].[PARENT] & '\' AS PATH, " & _
"[temp_" & strOneTOA_Name & "].LV, [ITEM].QUANTITY FROM temp_TOA_J04 INNER JOIN ITEM ON [temp_" & strOneTOA_Name & "].[CHILD] = ITEM.PARENT WHERE " & _
"((([temp_" & strOneTOA_Name & "].[LV])= " & iLevel & " ));"


But this works with your double quotes, because when it parses and encounters two successive double-quotes,
it inserts ONE occurrence into the target string.


strSQL = "SELECT ITEM.PARENT, [temp_" & strOneTOA_Name & "].[PATH] & [ITEM].[PARENT] & ""\"" AS PATH, " & _
"[temp_" & strOneTOA_Name & "].LV, [ITEM].QUANTITY FROM temp_TOA_J04 INNER JOIN ITEM ON [temp_" & strOneTOA_Name & "].[CHILD] = ITEM.PARENT WHERE " & _
"((([temp_" & strOneTOA_Name & "].[LV])= " & iLevel & " ));"


hth,
Wayne

 

[gblack]

Sorry Wayne,

After the fact I realized why the double quotes didn't work... it's because I am also trying to add an ampersand WITHIN the strSQL...If you do this, access will post your backslassh with no quotes or double double quotes...neither work.

There are a number of ways, I found, to solve this.

First is using the single quotes shown above (I didn't try actual single quotes, I used chr(39)). I am told you can run into problems using ASCII if the overall string is too long, but mine wasn't

Second is to add a field in the table itself that filled only with a backslash, then your code would look like:

...[PATH] & [ITEM].[PARENT] & [ITEM].[New_Ampersand_field]


Third is something a coworker told me... and it uses 4 double quotes and variables for the field names. I have no idea why... but this works:

"[" & strPath & "] & [" & strPARENT & "] & " & """" & "\" & """"

I figured, if someone is having issues with this... here's all the ways I could find to skin that cat.

GL!
-Gary