Help for building a query string with double quotation mark, thanks a lot!

[xpengi]

hi,

i have a query which include some double quotation marks as below, (T
is table's variable name)

SELECT Switch(IsNull(Amount),"NULL",Amount<=10000,"0 t
10000",Amount<=20000,"10001 to 20000",Amount>20000,"20001 and up") A
Range, Count(SISID) AS NumStudents
FROM Tb
GROUP BY Switch(IsNull(Amount),"NULL",Amount<=10000,"0 t
10000",Amount<=20000,"10001 to 20000",Amount>20000,"20001 and up");


i want to build it as query sting in code,
for example,
...
dim strSQL as string

strSQL = "Select ...????..from " & Tb & "Group...????..."
...

how to deal with "" marks which is already in query?

Really thank

 

[Marshall Barton]

i have a query which include some double quotation marks as below, (Tb
is table's variable name)

SELECT Switch(IsNull(Amount),"NULL",Amount<=10000,"0 to
10000",Amount<=20000,"10001 to 20000",Amount>20000,"20001 and up") AS
Range, Count(SISID) AS NumStudents
FROM Tb
GROUP BY Switch(IsNull(Amount),"NULL",Amount<=10000,"0 to
10000",Amount<=20000,"10001 to 20000",Amount>20000,"20001 and up");

i want to build it as query sting in code,
for example,
..
dim strSQL as string

strSQL = "Select ...????..from " & Tb & "Group...????..."
..
how to deal with "" marks which is already in query?

THe rule for quotes within quotes is to use two quotes where
you want one quote in the resulting string:

strSQL ="SelectSwitch(IsNull(Amount), ""NULL"", " _
& "Amount<=10000, ""0 to 10000"", " _
& "Amount<=20000, ""10001 to 20000"", " _
& "Amount>20000,""20001 and up"") " _
& "AS Range, Count(SISID) AS NumStudents " _
& "FROM " & Tb & " " _
& "GROUP BY SelectSwitch( . . . ) "

Some people find that difficult to read so they concatenate
the Chr(34) function instead:

strSQL ="SelectSwitch(IsNull(Amount), " & Chr(34) & _
"NULL" & Chr(34) & Chr(34) & _
"0 to 10000" & Chr(34) & ", " _
. . .
while others find that too cluttered to be practical.

Alternatively, in SQL, you can use apostrophes in place of a
quote:

strSQL ="SelectSwitch(IsNull(Amount), 'NULL', " _
& "Amount<=10000, '0 to 10000', " _
. . .