What I'm trying to do is have the sub call the underlying query and apply the case statement to the where parameter of the query, based on the selction in the option group. I would also like to show all of the records as the default value, then apply the case statement only if one of the option group controls are checked. My query is a crosstab query, is that the reason this does not work?
Here is the query I am calling:
TRANSFORM Count(tblOut.OutID)/active AS Cnt
SELECT tblOut.txtDept, tblOut.RelatedEvent, qryAllOut.active AS Active, Count(tblOut.OutID) AS Out, out/active AS Total FROM qryAllOut INNER JOIN tblOut ON qryAllOut.Dept = tblOut.txtDept GROUP BY tblOut.txtDept, tblOut.RelatedEvent, qryAllOut.active PIVOT Format([CallDate],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");
Here is the Sub:
Private Sub Form_Open(Cancel As Integer)
Dim strsql As String
Select Case fraEvent
Dim relatedevent As String
Case 1
relatedevent = "NA"
Case 2
relatedevent = "Severe Weather"
Case 3
relatedevent = "Sports Event"
Case 4
relatedevent = "Holiday"
Case 5
relatedevent = "Worked Overtime"
Case 6
relatedevent = "Local Incident"
Case 7
relatedevent = "Traffic"
End Select
strsql = "SELECT * " _
& "FROM qrycrosstab " _
& "WHERE relatedevent " & relatedevent & ""
Forms!frmplan.Form.RecordSource = strsql
End Sub
Thanks!
Here is the query I am calling:
TRANSFORM Count(tblOut.OutID)/active AS Cnt
SELECT tblOut.txtDept, tblOut.RelatedEvent, qryAllOut.active AS Active, Count(tblOut.OutID) AS Out, out/active AS Total FROM qryAllOut INNER JOIN tblOut ON qryAllOut.Dept = tblOut.txtDept GROUP BY tblOut.txtDept, tblOut.RelatedEvent, qryAllOut.active PIVOT Format([CallDate],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");
Here is the Sub:
Private Sub Form_Open(Cancel As Integer)
Dim strsql As String
Select Case fraEvent
Dim relatedevent As String
Case 1
relatedevent = "NA"
Case 2
relatedevent = "Severe Weather"
Case 3
relatedevent = "Sports Event"
Case 4
relatedevent = "Holiday"
Case 5
relatedevent = "Worked Overtime"
Case 6
relatedevent = "Local Incident"
Case 7
relatedevent = "Traffic"
End Select
strsql = "SELECT * " _
& "FROM qrycrosstab " _
& "WHERE relatedevent " & relatedevent & ""
Forms!frmplan.Form.RecordSource = strsql
End Sub
Thanks!