SQL with pivot expression (Access 2000)

Topic

New Reply

SQL with Pivot expression

The following expression is a part of my sql clause and does not work:

dim strPivot as String
strPivot = “PIVOT Qtr & DatePart(q,[InvoiceDate],1,0) In (Qtr 1,Qtr 2,Qtr 3,Qtr 4)”

When i put this expression in an sql clause, i receive an error mssing operator.
Without the strPivot expression, my SQL works.Do i have some errors in my expression?

Reply | Quote

Replies

I think you need some quotes. I tried the following and it worked nicely:

TRANSFORM Count(1) AS CountOfADate
SELECT Count(1) AS [Total Of ADate]
FROM RandomDates
GROUP BY 1
PIVOT “Qtr” & DatePart(“q”,[Adate]) In (“Qtr1″,”Qtr2″,”Qtr3″,”Qtr4”);

(where RandomDates is a table containing a field ADate full of random dates).

Reply | Quote