Quotes within quotes (repost)

[Rajat]

Hi,

I am constructing a string which encloses an SQL stmt:

strQuery = "TRANSFORM Sum(tblCurrentMonth.[Total Price])
AS [SumOfTotal Price] " & _
"SELECT tblCurrentMonth.StdEC, tblExpenseCodes." &
Me.cboManagerLevel & " AS [Report Client], tblCurrentMonth.
[Billing Cycle Name], Sum(tblCurrentMonth.[Total Price])
AS Total " & _
"FROM tblCurrentMonth INNER JOIN tblExpenseCodes ON
tblCurrentMonth.StdEC = tblExpenseCodes.StdEC " & _
"WHERE ((([tblExpenseCodes]." & Me.cboManagerLevel & ")
= '" & strReportClient & "')) " & _
"GROUP BY tblCurrentMonth.StdEC, tblExpenseCodes.Manager,
tblCurrentMonth.[Billing Cycle Name] " & _
"PIVOT tblCurrentMonth.[Product Name] In ('Data
Center','Desktop','Internet Hosting','Market
Data','Messaging &
Mobile', 'Network', 'Other', 'Projects', 'Technology
Requests (MAC's)','Voice & Conferencing');"

The problem is that MAC's comes with its own apostrophe
which is part of the data.

I would need a third quoting mechanism or an escape char
that tells the program that this is part of the data.

Please suggest a way.

I tried doing MAC''s using Doug Steele's idea but it
didn't help.

Thanks,

Rajat

 

[George Nicholson]

you can try using Chr(39):

"MAC" & CHR(39) & "s" = MAC's

HTH,

George Nicholson

Remove 'Junk' from return address.

 

[Alex Ivanov]

Try to paste the value of your strQuery into Query Analyzer and make it
work.

 

[Chris Nebinger]

Try using:
"PIVOT tblCurrentMonth.[Product Name] In (""Data
Center"",""Desktop"",""Internet Hosting"",""Market
Data"",""Messaging &
Mobile"", ""Network"", ""Other"", ""Projects"", ""Technolog
y Requests (MAC's)"",""Voice & Conferencing"");"


Chris Nebinger

 

[Rajat]

Thanks, Chris. That worked.
Rajat

-----Original Message-----
Try using:
"PIVOT tblCurrentMonth.[Product Name] In (""Data
Center"",""Desktop"",""Internet Hosting"",""Market
Data"",""Messaging &
Mobile"", ""Network"", ""Other"", ""Projects"", ""Technolo g
y Requests (MAC's)"",""Voice & Conferencing"");"


Chris Nebinger

-----Original Message-----

Hi,

I am constructing a string which encloses an SQL stmt:

strQuery = "TRANSFORM Sum(tblCurrentMonth.[Total Price])
AS [SumOfTotal Price] " & _
"SELECT tblCurrentMonth.StdEC, tblExpenseCodes." &
Me.cboManagerLevel & " AS [Report Client], tblCurrentMonth.
[Billing Cycle Name], Sum(tblCurrentMonth.[Total Price])
AS Total " & _
"FROM tblCurrentMonth INNER JOIN tblExpenseCodes ON
tblCurrentMonth.StdEC = tblExpenseCodes.StdEC " & _
"WHERE ((([tblExpenseCodes]." & Me.cboManagerLevel & ")
= '" & strReportClient & "')) " & _
"GROUP BY tblCurrentMonth.StdEC, tblExpenseCodes.Manager,
tblCurrentMonth.[Billing Cycle Name] " & _
"PIVOT tblCurrentMonth.[Product Name] In ('Data
Center','Desktop','Internet Hosting','Market
Data','Messaging &
Mobile', 'Network', 'Other', 'Projects', 'Technology
Requests (MAC's)','Voice & Conferencing');"

The problem is that MAC's comes with its own apostrophe
which is part of the data.

I would need a third quoting mechanism or an escape char
that tells the program that this is part of the data.

Please suggest a way.

I tried doing MAC''s using Doug Steele's idea but it
didn't help.

Thanks,

Rajat


.

.