How to count the number of events based on type? (1 Viewer)

[hclifford]

Hi all,

I have a table that contains many records of events. I've already created a multiple item form based on the table. My form contains 2 textboxes for input of a date range and a dropdown box to select "yes or no".

What I am having difficulty with is the query that will count the records by type insert it into the recordsource such that the multiple item form will appear like this:

Event Type| Count
Input | 4
Output | 2
Update | 3

How do I go about doing so?

I have been testing with this simple query:

Private Sub Command8_Click()
    Dim Task As String
    
    Task = "SELECT EventType, Count(EventType) FROM Final GROUP BY EventType;"
    Me.RecordSource = Task
End Sub

But the column that contains the count keeps appearing as #name?

 

[PeterF]

What is the source (fieldname) of the column with #name?
The way your SQL works the fieldname will be something like "Expr....";
Change your SQL to

"SELECT EventType, Count(EventType) [COLOR="Red"]AS CountOfEventType [/COLOR]FROM Final GROUP BY EventType;"

And set the source of your column to "CountOfEventType"

 

[hclifford]

What is the source (fieldname) of the column with #name?
The way your SQL works the fieldname will be something like "Expr....";
Change your SQL to

"SELECT EventType, Count(EventType) [COLOR="Red"]AS CountOfEventType [/COLOR]FROM Final GROUP BY EventType;"

And set the source of your column to "CountOfEventType"

Hi Peter! I just did as you said and it worked perfectly!

I'm facing another problem now. Every time I run the search code to count the records, and I leave my date fields blank, I would receive this error "Run-time error 94. Invalid use of null."

How do I rectify this? I already have a code to handle null values (I think).

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

 

[PeterF]

You Dim startDate as date
If your entry field is empty "startDate = Me.txtStartDate" wil give error 94

 

[smig]

what do you expect to get if you leave your Dates fields blank ?

 

[hclifford]

Since each table contains the record for the month, I want the count for each event type for the entire month to be displayed.

 

[hclifford]

Well, it works if I change the data type to string and remove the variables. But is this recommended?

Private Sub Command8_Click()
    Dim Task As String
    Dim startDate As String
    Dim endDate As String
    
    
    If Trim(txtStartDate & "") = vbNullString And Trim(txtEndDate & "") = vbNullString Then
        Task = "SELECT EventType, Count(EventType) As CountOfEventType FROM Final 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;"
    End If
    Me.RecordSource = Task
End Sub

 

[namliam]

Well in your new version you dont need the variables at all, you simply fetch the dates from the form

Remember to indent your code and kudos for having readable SQL

 

[hclifford]

Well in your new version you dont need the variables at all, you simply fetch the dates from the form

Remember to indent your code and kudos for having readable SQL

Noted!

I can't seem to get this part to work correctly. It's basically for when no date range is input, but the user selects the yes/no dropdown list. When I tested this query, my data in the table didn't even change.

PS, I changed Combo9 to "acknowledgement"

ElseIf InStr(Me.acknowledgement, "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.acknowledgement, "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;"

 

[namliam]

Is your field a yes/no field? if so then it is really a True/False field

 

[hclifford]

Is your field a yes/no field? if so then it is really a True/False field

Wellllll, I created it by using the wizard and filled in my own values. Does that count?

 

[hclifford]

I managed to solve it already!

I used the debug.print to check my Task and it turns out that the SQL query that was actually running belonged to the first If statement.

 

[namliam]

well done