I am having difficulty getting my sort function to work on a report. Below is the code being used in the report:

Private Sub Report_Open(Cancel As Integer)

mainsql = "SELECT Quote_Number, Company_Name, Date, Contact_Name, Contact_Number, PreparedBy FROM Quotations "
addSQL = ""
If Forms![Main Reports]!Sorting1 <> "(Not Sorted)" Then
addSQL = "ORDER BY " & Forms![Main Reports]!Sorting1
If Forms![Main Reports]!Choice1 = 1 Then
addSQL = addSQL & " DESC"
End If
If Forms![Main Reports]!Sorting2 <> "(Not Sorted)" Then
addSQL = addSQL & ", " & Forms![Main Reports]!Sorting2
If Forms![Main Reports]!Choice2 = 1 Then
addSQL = addSQL & " DESC"
End If
If Forms![Main Reports]!Sorting3 <> "(Not Sorted)" Then
addSQL = addSQL & "," & Forms![Main Reports]!Sorting3
If Forms![Main Reports]!Choice3 = 1 Then
addSQL = addSQL & " DESC"
End If
End If
End If
End If
mainsql = mainsql + addSQL

RecordSource = mainsql
End Sub


The sorting is supposed to be controlled by 3 sort boxs on the form 'Main Reports' form which has a drop down menu to select which field is sorted by (Sorting1, sorting2, sorting3). Within these boxes there is a toggle switch for assending/decending (Choice1, choice2,choice3).

Currently whenever any of the sort boxes are given a value to sort by it does not work and no sorting is applied to the report. I cant seem to figure out why its not working :confused:

Sorting for a report has to be set up in Sorting and Grouping. I do this type of thing to change the sort:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms!frmReports.fraGrouping
Case 1 'group on car type
Me.GroupLevel(0).ControlSource = "CarType"
Me.txtGroup.ControlSource = "CarDesc"
Case 2 'group on company
Me.GroupLevel(0).ControlSource = "Company"
Me.txtGroup.ControlSource = "Company"
Case 3 'sort on date/time, no grouping
Me.GroupLevel(0).ControlSource = "DispDateTime"
Me.txtGroup.ControlSource = "DispDateTime"
Me.GroupHeader0.Visible = False
End Select
End Sub

Thanks for that code, could you please describe it in a bit of detail and explain how it would fit in with the code I provided? Thanks

Can the user select more than one sort order?

Yes, the user is given a choice of 3 search orders

What I meant was can they select more than one sort order at the same time or are they limited to just one sort order?

Yes, they can choose up to 3 sort orders at the same time

So you need to build the order by statement correctly
You do not saty what the other options are though (ASC, DESC)
If Combo1 <> "not sorted" then
strSort = "FieldName, "
End If

If Combo2 <> "not sorted" then
strSort = strSort & "FieldName, "
End If

If Combo3 <> "not sorted" then
strSort = strsort & "FieldName, "
End If


strsort = trim(strsort) :- Remove last space
if len(strsort) > 0 then
strsort = "ORDER BY " & left(strsort,Len(strsort)-1) :-Remove last comma
End If

This goes in the form or the report?

Wether it is sorted ASC or DESC depends on a toggle switch below the sort choice. Like this:
http://i1021.photobucket.com/albums/af333/Andycabbages/Sort.jpg
How would I impliment that? Just add another IF statement within the current IF statement?

What is appearing in your combo boxes? Field names?

Yes, field names, there are 5 possible field names that you can sort by:

Quote_Number
Company_Name
Date
Contact_Name
Contact_Number

These are all fields in a 'Quotations' table

Sorry, these 5 fields are taken from a Queury named 'QuotationsList'. It is this same queury that the report is derrived from.

And I am assuming that you have handled the trapping of making the same selection in more than one combo at the same time.

If Combo1 <> "not sorted" then
strSort = "[" & Me.Combo1 & "]" & IIF(Me.Choice1 = 1, " DESC,",", ")
End If

If Combo2 <> "not sorted" then
strSort = "[" & Me.Combo2 & "]" & IIF(Me.Choice2 = 1, " DESC,",", ")
End If

If Combo3 <> "not sorted" then
strSort = "[" & Me.Combo3 & "]" & IIF(Me.Choice3 = 1, " DESC,",", ")
End If




strsort = trim(strsort) :- Remove last space
if len(strsort) > 0 then
strsort = "ORDER BY " & left(strsort,Len(strsort)-1) :-Remove last comma
End If
Debug.Print strsort

I now have the following code:

Private Sub Image126_Click()

If Sorting1 <> "(Not Sorted)" Then
strsort = "[" & Me.Sorting1 & "]" & IIf(Me.Choice1 = 1, " DESC,", ", ")
End If

If Sorting2 <> "(Not Sorted)" Then
strsort = "[" & Me.Sorting2 & "]" & IIf(Me.Choice2 = 1, " DESC,", ", ")
End If

If Sorting3 <> "(Not Sorted)" Then
strsort = "[" & Me.Sorting3 & "]" & IIf(Me.Choice3 = 1, " DESC,", ", ")
End If


strsort = Trim(strsort)
If Len(strsort) > 0 Then
strsort = "ORDER BY " & Left(strsort, Len(strsort) - 1)
End If
Debug.Print strsort

DoCmd.OpenReport "QuotationsList", acViewPreview

The report prints out fine, but still none of the sorting selections are working

Well that's because you have not included the strSort string into your main sql string

mainsql = mainsql + strsort

RecordSource = mainsql

Of course these are incorrect.

Firstly you needs to delcare the strSort as a public variable in a standard module not in the form.

Then on the OnLoad of the Report you would code

Me.Recordsource = "Select * From ...." & strSort

Or

Remove the "ORDER BY" from the front of the strSort string and

Me.SortOrder = strSort

I see. Would you mind drawing up a mock of what the code in the 'Private Sub Report_Open(Cancel As Integer)' would look like?