Dates in Pivot Table

[wtkaufmann]

I have dropped Dates into the column field of a Pivot Table and have it sorted by Quarters. The default is to display "Qrt 1" "Qrt 2" and so on...

I would like to display the last day of the quarter.. So "Qrt 1" would equal "3/31/2010" and "Qrt 2" would show "6/30/2010"

When I switch the table's properties to show 'short date' it shows "1/1/1900" and "1/2/1900'' and so on... Any Help to display the last day in the quarter of the current year?

 

[boblarson]

First create a query which will create those dates for you.

Something like this:

SELECT Switch(DatePart("q",[OrderDate])=1,#3/31/2010#,DatePart("q",[OrderDate])=2,#6/30/2010#,DatePart("q",[OrderDate])=3,#9/30/2010#,DatePart("q",[OrderDate])=4,#12/31/2010#) AS Qtr
FROM Orders;

But instead of hardcoding the dates, it would be better to use:

SELECT Switch(DatePart("q",[OrderDate])=1,DateSerial(Year([OrderDate]),3,31),DatePart("q",[OrderDate])=2,DateSerial(Year([OrderDate]),6,30),DatePart("q",[OrderDate])=3,DateSerial(Year([OrderDate]),9,30),DatePart("q",[OrderDate])=4,DateSerial(Year([OrderDate]),12,31)) AS Qtr
FROM Orders;

 

[wtkaufmann]

Would code go into the query that creates my form?
OR
Should I use a query Pivot Table instead of a form Pivot Table?

I am a little confused, any more direction you could give would be great