Count data items by name

[msuguy71]

Hi all,

I am trying to create a report that will count a data item and group it by name. I found the following code and it mostly works:

Private Sub RunReport_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "TotalRNCalls"      'Put your report name in these quotes.
    strDateField = "[CallDate]" 'Put your field name in the square brackets in these quotes.
    strCCTRN = "[CCTRN]"
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartdate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartdate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEnddate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEnddate + 1, strcJetDate) & ")"
    End If
        
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere
        
Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

This creates a report that looks like this for a user selected date range:

Tim 1
Tim 1
Tim 1
Sam 1
Sam 1

I would like it to look like the following:

Tim 3
Sam 2

I used to use a query and a form which worked, the SQL of the query was:

SELECT Count(CCTLog.[Run#]) AS [CountOfRun#], CCTLog.CCTRN.Value, CCTLog.CallDate
FROM CCTLog
GROUP BY CCTLog.CCTRN.Value, CCTLog.CallDate;

Because I was using macro events to open the query and the report, I had to create a new date range form for each report because the macro event would only let me specify a specific report name to open, and someone told me I could avoid that using VBA and only having to have one generic date range form.

I don't see a GROUP BY command in VBA, and I have not been very successful finding an answer.

Can anyone help?

Thanks,



Rick

[amrut]

Enter you date criteria on form using text boxes. Use these text boxes as criteria for your query. Once your query shows desired results, create a report based on this query and you are done ! No code is required.

[msuguy71]

Amrut,

Thanks for the reply. That was what I was doing, but I had to create a new date range form for every report that I wanted to run. I was getting too many forms.

[amrut]

Use a form with 2 text boxes say txtStartDate and txtEndDate formatted as short date and a command button.
Then use

Between Forms!YourFormName!txtStartDate and Forms!YourFormName!txtEndDate

in the query's CallDate column as criteria . Enter the date range required in text boxes and open the report on a click of a button on this form.
You do not need multiple forms.

[msuguy71]

Use a form with 2 text boxes say txtStartDate and txtEndDate formatted as short date and a command button.
Then use

Between Forms!YourFormName!txtStartDate and Forms!YourFormName!txtEndDate

in the query's CallDate column as criteria . Enter the date range required in text boxes and open the report on a click of a button on this form.
You do not need multiple forms.

But what do I put for the form name in the macro of the command button? I have 10 different report names to run. How does the date range form know which report to open?

I think I am missing an important piece of information.

[June7]

The procedure you have should be adequate to build filter criteria and open report filtered to specified date range. If the code is behind the form with the date input boxes, form name is not needed. The code references the form and textboxes with:

Me.txtEnddate and Me.txtStartDate

Me. is alias for the form name.

txtEndDate and txtStartDate are textbox names.

Now use report Grouping and Sorting features with aggregate calcs in textboxes in group footer.

Create a group based on the name field and expression in textbox would be: =Count(*)

Alternative to the procedure constructing filter criteria is a dynamic parameterized query. http://www.datapigtechnologies.com/f...mtoreport.html

Regardless of which method, still need the report set up with Grouping and aggregate calcs.

[msuguy71]

Hi,

Thanks for your help, but I am still not understanding. Here is what I have:

1. From the SwitchBoard, a user selects the report they want to run.
2. The switchboard opens that report.
3. The report calls up a form called DateRange on open.
4. User enters a start date and end date using the date picker.
5. The user hits a command button that calls up the code above which opens the report.

I tried having just the date range form, query, and report but I don't understand what is supposed to open what.

Thanks,

Rick

[June7]

The report should not call up form.

Open form, input date range, open report.

[msuguy71]

Sorry for being so dense, but I am not sure how that would work.

So if I open the date range form from the switchboard, how does the form know which report to open?

For example:

The switchboard looks like this:

Reports:

- Total transport by RN
- Transport log
- Total transports

The user would select the report they want, the date range form opens, but how do I open the report desired?

Transport Log --> Opens date range form --> user enters start and end dates in text boxes --> what opens the transport log report?

I am trying to find a video or something online to spell it out for me, but I am not having much luck.

Rick

[June7]

Are you using form created by switchboard wizard? Nasty thing, I never used it. I generally avoid the design wizards.

Just have to get actions in proper sequence.

You can either have the date range inputs on switchboard or have switchboard open a form for inputting criteria and select report on that form. Can be button for each report or a listbox of choices.

Sample VBA code:

DoCmd.OpenForm "DateRange", , , , , acDialog
'code to construct report filter criteria
DoCmd.OpenReport "report name", , , strWHERE

The acDialog parameter causes code execution on the calling form to be suspended until the DateRange form closes. Will need some code behind DateRange to pass input values back to the calling form.

Or
DoCmd.OpenForm "DateRange"

Then code behind the DateRange form:
'code to construct report filter criteria
DoCmd.OpenReport "report name", , , strWHERE

or if using the parameterized query approach, no code to construct filter criteria and just open report
DoCmd.OpenReport "report name"

If report is open in report view (not print preview), can select criteria afterward and refresh the report. Not something I've ever set up but have seen it done. This would use the parameterized query as RecordSource approach or code to set the report Filter property.