Escape char or Quoting char

  • Thread starter Thread starter Rajat
  • Start date Start date
R

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
 
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,
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 &
Click to expand...
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
Click to expand...


.
Click to expand...
 
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!)


Rajat said:
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 &
Click to expand...
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
Click to expand...


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

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
 
Back
Top