Crosstab Date Help

[Slimjimmpc]

Trying to take todays date then show all records that are 12 months before the date. e.g. March 2010-Feb 2011
Set up just like a rolling financial report.

The problem is in my coding somewhere.

PIVOT Year([Date])*12+Format([Date],"mm")-(Year(Date())*12+Format(Date(),"mm"))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

All the table is blank but shows 1-12 in the columns. Tried Date() and Now() but neither work with In (1,2,3,4,5,6,7,8,9,10,11,12).

Thanks

 

[vbaInet]

You need a WHERE clause of

[DateField] >= DateAdd("m", -12, Date())

Then your PIVOT will be something like

PIVOT Format("mmm yyyy", [DateField])

 

[Slimjimmpc]

it works but i need to be able to put it into a report.

was planning to use In (1,2,3,4,5,6,7,8,9,10,11,12) then i can use those on the report and it would auto update.

When i add In (1,2,3,4,5,6,7,8,9,10,11,12) to the end of the pivot i get the 1-12 but the cells are blank

 

[vbaInet]

You will need to dynamically create the PIVOT part of the query like this (aircode):

dim qdf as dao.querydef, tempDate as date
dim i as integer, strPivot as string
 
const strSQL as string = "TRANSFORM ... " & _
                         "PIVOT Format('mmm yyyy', [DateField]) IN ("
 
set qdf = currentdb.querydefs("QueryName")
 
tempDate = dateadd("m", -13, Date)
 
' build IN part
for i = 1 to 12
    strpivot = strpivot & "'" & format(dateadd("m", i, tempDate), "mmm yyyy") & "', "
next
 
' strip off last two trailing characters
strpivot = left(strpivot, len(strpivot) - 2)
 
' set the sql of the querydef
qdf.sql = strsql & strpivot & ");"
 
Currentdb.querydefs.refresh

Then you have to open the report in design view, set the control sources of the twelve textboxes then open the report in print preview.

I suppose you can set the Control Sources inside the loop.

But why use a report anyway when you already have the crosstab query.