Escape char or Quoting char

[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.

Thanks,

Rajat

 

[Douglas J. Steele]

Try doubling the quote:

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

 

[Rajat]

Doug,
That didn't help. Any other ideas?
Thanks,
Rajat

-----Original Message-----
Try doubling the quote:

"PIVOT tblCurrentMonth.[Product Name] In ('Data Center','Desktop','Internet
Hosting','Market Data','Messaging &

Mobile', 'Network', 'Other', 'Projects',

'Technology Requests (MAC''s)','Voice & Conferencing');"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

Thanks,

Rajat

.

 

[Douglas J. Steele]

Are you getting an error message? If so, what is it? If not, is it just a
case that you're not getting that specific product back? If so, are you sure
you've typed it exactly as it is in the table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
That didn't help. Any other ideas?
Thanks,
Rajat

-----Original Message-----
Try doubling the quote:

"PIVOT tblCurrentMonth.[Product Name] In ('Data Center','Desktop','Internet
Hosting','Market Data','Messaging &

Mobile', 'Network', 'Other', 'Projects',

'Technology Requests (MAC''s)','Voice & Conferencing');"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

Thanks,

Rajat

.

 

[Tim Ferguson]

Doug,
That didn't help. Any other ideas?

Unless you are using SQL server, use double quotes for all the internal
strings (or at least for the ones containing apostrophes, but mixing will
probably cause more confusion):-

strSQL = ... & _
"PIVOT tblCurrentMonth.[Product Name] IN " & _
" (""Data Center"", ""Desktop"",""Internet Hosting"", " & _
" ""Market Data"", ""Messaging & Mobile"", ""Network"", " & _
" ""Other"", ""Projects"", ""Technology Requests (MAC's)", " & _
" ""Voice & Conferencing"");"


Are the ampersands legal (he asked timidly, never having understood PIVOT
table in SQL...)?

Hope it helps


Tim F