How do I code the multiple form's function to suit these needs?

[shawnnnnnnn]

Hi all

I have a table containing [Timestamp], [AgentName], [SNOCAcknowledged], [Details], [EventType].

I need to create a multiple item form that counts the records based on event type and displays it like in the image.

The form will be able to even count the number of event types by a date range and/or if it was acknowledged or not.

As you can see in the image, I have successfully managed to do the basic stuff for this. However, my problem lies with the acknowledgement.

When I leave the date range blank and select an acknowledgement to search, the data does not change at all.

My current codes are:

Private Sub Command8_Click()
    Dim Task As String
 
    If Trim(txtStartDate & "") = vbNullString And Trim(txtEndDate & "") = vbNullString Then
        Task = "SELECT EventType, Count(EventType) As CountOfEventType FROM Final GROUP BY EventType;"
 
    ElseIf InStr(Me.Combo9, "Yes") And Trim(txtStartDate & "") = vbNullString And Trim(txtEndDate & "") = vbNullString Then
    Task = " SELECT EventType, Count(EventType) As CountOfEventType " & _
           " FROM Final " & _
           " WHERE Final.SNOCAcknowledged='Yes' " & _
           " GROUP BY EventType;"
 
    ElseIf InStr(Me.Combo9, "No") And Trim(txtStartDate & "") = vbNullString And Trim(txtEndDate & "") = vbNullString Then
    Task = " SELECT EventType, Count(EventType) As CountOfEventType " & _
           " FROM Final " & _
           " WHERE Final.SNOCAcknowledged='No' " & _
           " GROUP BY EventType;"
 
    ElseIf Trim(Me.Combo9 & "") = vbNullString Then
    Task = " SELECT EventType, Count(EventType) As CountOfEventType " & _
           " FROM Final " & _
           " WHERE Final.Timestamp BETWEEN #" & Format(Me.txtStartDate, "MM/DD/YYYY HH:MM:SS AM/PM") & "# " & _
                                     " AND #" & Format(Me.txtEndDate, "MM/DD/YYYY HH:MM:SS AM/PM") & "# " & _
                                     " GROUP BY EventType;"
 
    ElseIf InStr(Me.Combo9, "Yes") Then
    Task = " SELECT EventType, Count(EventType) As CountOfEventType " & _
           " FROM Final " & _
           " WHERE Final.SNOCAcknowledged='Yes' AND Final.Timestamp BETWEEN #" & Format(Me.txtStartDate, "MM/DD/YYYY HH:MM:SS AM/PM") & "# " & _
                                     " AND #" & Format(Me.txtEndDate, "MM/DD/YYYY HH:MM:SS AM/PM") & "# " & _
                                     " GROUP BY EventType;"
 
    ElseIf InStr(Me.Combo9, "No") Then
    Task = " SELECT EventType, Count(EventType) As CountOfEventType " & _
           " FROM Final " & _
           " WHERE Final.SNOCAcknowledged='No' AND Final.Timestamp BETWEEN #" & Format(Me.txtStartDate, "MM/DD/YYYY HH:MM:SS AM/PM") & "# " & _
                                     " AND #" & Format(Me.txtEndDate, "MM/DD/YYYY HH:MM:SS AM/PM") & "# " & _
                                     " GROUP BY EventType;"
 
    End If
    Me.RecordSource = Task
End Sub
 

PS, my dropdown list was actually created using the wizard and filling in my own "yes" and "no", so I don't think changing the thing to true or false helps.

[shawnnnnnnn]

I managed to solve it already. I used the debug print to check my Task and it turns out that it was executing the first if statement as the date range was blank.