[Beth1]

Hi I need to change an Access based report. It is based on a crosstab query. Currently the columns on the report run Jan to Dec. I need to change that so they run based on startdate. The user enters the date parameters into the form and the query sends the information for that date frame to the report, but every time it prints out Jan to Dec.

If the users Start date is Aug I need the report to run Aug to Jul. Does anyone at this sight know how to code this type of report?

This is what I have so far but I am having error messages on the if Statement and I can not find the problem with the help feature.
Dim app As New Access.Application
Dim Rpt As Report
Dim ctl As Control
Dim strSQL As String
Dim db As Database
Dim qry As QueryDef
Set db = CurrentDb
Set qry = db.QueryDefs("qryincidentbymonth")

strSQL = "TRANSFORM Count(qryallstudy.STUDY_DETAIL_ID) AS CountOfSTUDY_DETAIL_ID SELECT qryallstudy.STUDY_DESC, Count(qryallstudy.STUDY_DETAIL_ID) AS [Total OfSTUDY_DETAIL_ID] FROM qryallstudy "
strSQL = strSQL & "WHERE (((qryallstudy.OCC_DATE) Between #" & Me.StartDate & "# And #" & Me.End_Date & "# )) "
strSQL = strSQL & "GROUP BY qryallstudy.STUDY_DESC PIVOT Format([OCC_DATE],""" & "mmm" & """) In (""" & "Jan" & """,""" & "Feb" & """,""" & "Mar" & """,""" & "Apr" & """,""" & "May" & """,""" & "Jun" & """,""" & "Jul" & """,""" & "Aug" & """,""" & "Sep" & """,""" & "Oct" & """,""" & "Nov" & """,""" & "Dec" & """));"

DoCmd.OpenReport "Incident by Month", acViewDesign

If Rpt.Name = "Incident by Month" Then
Select Case Format(Me.StartDate, "mm")
Case "01"
ctl.Properties.Item("Caption") = "JAN"
ctl.Properties.Item("Caption") = "FEB"
ctl.Properties.Item("Caption") = "MAR"
ctl.Properties.Item("Caption") = "APR"
ctl.Properties.Item("Caption") = "MAY"
ctl.Properties.Item("Caption") = "JUN"
ctl.Properties.Item("Caption") = "JUL"
ctl.Properties.Item("Caption") = "AUG"
ctl.Properties.Item("Caption") = "SEP"
ctl.Properties.Item("Caption") = "OCT"
ctl.Properties.Item("Caption") = "NOV"
ctl.Properties.Item("Caption") = "DEC"

i do not use crosstabs directly (typicaly custom coded in asp...) but your where statement

Code:

WHERE (((qryallstudy.OCC_DATE) Between #" & Me.StartDate & "# And #" & Me.End_Date & "# )) "

should be limiting the results you are getting. Is that part working? if so then you just want to hide the coloumns from showing correct?