Solved How to Sort report by 2 fields

[tihmir]

Hi all, I need to Sort my report by 2 fields.
Now when I open my report it is sorted by date. But I want next to sort by second field sort "name"
I filter my report with this code:

Private Sub cmd_Report_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 = "rpt_DailyReport"      'Put your report name in these quotes.
    strDateField = "[Date]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtFrom) Then
        strWhere = "(" & strDateField & " >= " & Format((Me.txtFrom) , strcJetDate) & ")"
        Exit Sub
    End If
    
    If IsDate(Me.txtTo) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & FormatMe.txtTo + 1, strcJetDate) & ")"
        Exit Sub
    End If
            
    If IsNull(cbo_Name) Then
        MsgBox "Please select name!", vbInformation, "Atention!"
        cbo_Name.SetFocus
        Exit Sub
    End If
    
    If Trim(Me.cbo_Name & "") <> "" And (Me.ccbo_Name & "") <> "- All -" Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Name] = '" & Me.cbo_Name & "'"
        Exit Sub
    End If
    
    ' check if the strWhere has some value
    If Trim(strWhere) = "" Then strWhere = "(1=1)"
        
    '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
    Reports(strReport).subrpt_Inspections.Report.Filter = strWhere
    Reports(strReport).subrpt_Inspections.Report.FilterOn = True
    Reports(strReport).subrpt_ExtraWork.Report.Filter = strWhere
    Reports(strReport).subrpt_ExtraWork.Report.FilterOn = True
    
Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    
    
    DoCmd.OpenReport "rpt_DailyReport, acViewReport
    DoCmd.SetOrderBy "Date ASC, Name ASC"
    
    Resume Exit_Handler
    
End Sub

 

[pbaldy]

Typically that's set up in Group & Sort, on the ribbon with the report in design view.

 

[CJ_London]

reports ignore any sorting in the recordsource and applies it's own through grouping and sorting. If you are getting the right order by date at the moment that is more due to luck than intent. I say this because your setOrderBy command is in your error handler so will only run if you get an error of any sort - but even that won't run because you have commented out the on error goto line. The code exits the sub where the line is 'exit sub'.

Suggest put your code above this line

And FYI, 'name' is a reserved word and should not be used for field names

 

[tihmir]

Typically that's set up in Group & Sort, on the ribbon with the report in design view.

reports ignore any sorting in the recordsource and applies it's own through grouping and sorting. If you are getting the right order by date at the moment that is more due to luck than intent. I say this because your setOrderBy command is in your error handler so will only run if you get an error of any sort - but even that won't run because you have commented out the on error goto line. The code exits the sub where the line is 'exit sub'.

Suggest put your code above this line

And FYI, 'name' is a reserved word and should not be used for field names

Thank you very much for your help and for the advice you gave me, CJ_London, pbaldy
I don't know why I tried to do some sorting in mine VBA when it is so logical to sort in my query from which the report is generated!
I fixed the mess I had created and now everything is working properly!
CJ_London, thanks again for a detailed explanation of my problems.

​

 

[pbaldy]

Glad you got it sorted.