Hi Below is my code. I am trying to take the columns JAN, FEB, MAR Ect. and make them variable based on Me.StartDate. So If a user typed in the date 8/1/2005 the first column would be August. I have a decent start here but I am not sure where my Case Statement should be to amke it work right can anyone help me?
If Me.StartDate = "" Or IsNull(Me.StartDate) Then
MsgBox "You must enter a start date"
Me.StartDate.SetFocus
Exit Sub
End If
If Me.End_Date = "" Or IsNull(Me.End_Date) Then
MsgBox "You must enter a end date"
Me.End_Date.SetFocus
Exit Sub
End If
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
For Each Rpt In Reports
If Rpt.Name = "Incident by Month" Then
For Each ctl In Rpt.Controls
Select Case ctl
WHEN ctl.Name = "Jan_Label", ctl.Properties.Item("Caption") = "Jan"
Else
WHEN ctl.Name = "Feb_Label", ctl.Properties.Item("Caption") = "Feb"
Else
WHEN ctl.Name = "Mar_Label", ctl.Properties.Item("Caption") = "Mar"
Else
WHEN ctl.Name = "Apr_Label", ctl.Properties.Item("Caption") = "Apr"
Else
WHEN ctl.Name = "May_Label", ctl.Properties.Item("Caption") = "May"
Else
WHEN ctl.Name = "Jun_Label", ctl.Properties.Item("Caption") = "Jun"
Else
WHEN ctl.Name = "Jul_Label", ctl.Properties.Item("Caption") = "Jul"
Else
WHEN ctl.Name = "Aug_Label", ctl.Properties.Item("Caption") = "Aug"
Else
WHEN ctl.Name = "Sep_Label", ctl.Properties.Item("Caption") = "Sep"
Else
WHEN ctl.Name = "Oct_Label", ctl.Properties.Item("Caption") = "Oct"
Else
WHEN ctl.Name = "Nov_Label", ctl.Properties.Item("Caption") = "Nov"
Else
WHEN ctl.Name = "Dec_Label", ctl.Properties.Item("Caption") = "Dec"
End Select
Next
End If
Next
stDocName = "Incident by Month"
DoCmd.OpenReport stDocName, acPreview
End Sub
Linear Mode
