Days of the month

[kevcar40]

Hi
I have created a crosstab query based on the user selecting a year and month on a user form

what i would like to do is order the result in the date order for the month

ie if April selected from 1/4/11 to 30/4/11 in the Column header
if March selected from 1/3/11 to 31/3/11 in the Column header

is this possible ?

thanks

kevin

 

[John Spencer]

Yes it is possible.

Simple solution would be to have a table with all the dates in it that are
relevant for your data. One record for each day in the range. Then join that
to your query. WIthout seeing your current query's SQL statement I can only
give you a generic query as an example.

Parameters [YearValue] Long, [MonthValue] Long;
TRANSFORM Sum(X)
SELECT Y
FROM CalendarTable LEFT JOIN YourTable
ON CalendarTable.TheDate = YourTable.SomeDateField
WHERE CalendarTable.TheDate between DateSerial([YearValue],[MonthValue],1)
AND DateSerial([YearValue],[MonthValue]+1,0)
GROUP BY Y
PIVOT Format(CalendarTable.TheDate,"dd/mm/yy")

You could also do something similar with a table that contained rows with the
number from 1 to 31 and use that to calculate the days of the month.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County