CODEWITHSUNDEEP
ms-accessvba
lhatcher1986
lhatcher1986

Reputation: 67

Access VBA Multiple Sorting Criteria

The Case: I have a form that has several fields of entry. A button contained within this form runs a query and generates a report based on the form entry fields.

I have created 3 comboboxes that will allow the user to sort the report on various criteria (ie. sort by Analyst name, then by Meeting Date, then by Ticker).

Combo Box 1 = Sort_By;
Combo Box 2 = Sort_By_2;
Combo Box 3 = Sort_By_3

The Code:

Private Sub Run_Query_Button_Click()
If Revisit_Check.Value = False Then
DoCmd.OpenQuery "Important Information Extracted"
DoCmd.Close
DoCmd.OpenReport "Important Information Extracted", acViewReport
DoCmd.SetOrderBy Sort_By Sort_By_2 Sort_By_3
Else
DoCmd.OpenQuery "Revisit"
DoCmd.Close
DoCmd.OpenReport "Revisit_Report", acViewReport
DoCmd.SetOrderBy Sort_By Sort_By_2 Sort_By_3
End If
End Sub

This code returns a syntax error. It does not sort on the three "sort by" criteria. If I use only on of the criteria in the following fashion:

Private Sub Run_Query_Button_Click()
If Revisit_Check.Value = False Then
DoCmd.OpenQuery "Important Information Extracted"
DoCmd.Close
DoCmd.OpenReport "Important Information Extracted", acViewReport
DoCmd.SetOrderBy Sort_By

The code runs properly and sorts on the given "sort by" value. If instead of using the form field comboboxes to sort, I use the actual field names, for instance:

Private Sub Run_Query_Button_Click()
If Revisit_Check.Value = False Then
DoCmd.OpenQuery "Important Information Extracted"
DoCmd.Close
DoCmd.OpenReport "Important Information Extracted", acViewReport
DoCmd.SetOrderBy "Analyst, Meeting Date, Ticker"
   ...

Everything works fine as well. Why, when I use the three sort criteria, do I get a syntax error? How can I sort on these three criteria?

Upvotes: 1

Views: 3425

Answers (1)

Newd
Newd

Reputation: 2185

Assuming Sort_By is the values you get in the comboboxes you are seeking you will need something more like this: 

Private Sub Run_Query_Button_Click()
    If Revisit_Check.Value = False Then
        DoCmd.OpenQuery "Important Information Extracted"
        DoCmd.Close
        DoCmd.OpenReport "Important Information Extracted", acViewReport
        DoCmd.SetOrderBy Sort_By & ", " & Sort_By_2 & ", " & Sort_By_3
    Else
        DoCmd.OpenQuery "Revisit"
        DoCmd.Close
        DoCmd.OpenReport "Revisit_Report", acViewReport
        DoCmd.SetOrderBy Sort_By & ", " & Sort_By_2 & ", " & Sort_By_3
    End If
End Sub

Notice the & ", " & to make it into the same kind of string as "Analyst, Meeting Date, Ticker" which you said had worked. Otherwise your sort you are sending in the first time would look like this: "AnalystMeeting DateTicker"

Upvotes: 1

Related Questions