Sorting A Report

[AndyCabbages]

Basically I have a report which I need sorting. This sorting should be controlled by 3 drop down sort options on the form 'Main Reports' (Sorting1, Sorting2, Sorting3). The code for the report is:

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
If Forms![Main Reports]!Sorting1 = "Quote_Number" Then
addSQL = "ORDER BY Quotations." & Forms![Main Reports]!Sorting1
Else
addSQL = "ORDER BY " & Forms![Main Reports]!Sorting1
End If
If Forms![Main Reports]!option1 = 1 Then
addSQL = addSQL & " DESC"
End If

If Forms![Main Reports]!Sorting2 <> "(Not Sorted)" Then
If Forms![Main Reports]!Sorting2 = "Quote_Number" Then
addSQL = "ORDER BY Quotations." & Forms![Main Reports]!Sorting2
Else
addSQL = addSQL & ", " & Forms![Main Reports]!Sorting2
End If
If Forms![Main Reports]!option2 = 1 Then
addSQL = addSQL & " DESC"
End If

If Forms![Main Reports]!Sorting3 <> "(Not Sorted)" Then
If Forms![Main Reports]!Sorting3 = "Quote_Number" Then
addSQL = "ORDER BY Quotations." & Forms![Main Reports]!Sorting3
Else
addSQL = addSQL & "," & Forms![Main Reports]!Sorting3
End If
If Forms![Main Reports]!option3 = 1 Then
addSQL = addSQL & " DESC"
End If
End If
End If
End If

mainsql = mainsql + addSQL

RecordSource = mainsql

End Sub

I have used the exact same code in another part of the database (with different names for the sort boxes) and it works absolutely fine. The report opens but none of the sort values are applied. I cant work out why on earth it is working in one place and not here

 

[PNGBill]

You shouldn't use date as a field / control name.

 

[AndyCabbages]

Its not my database so I didnt have a say in the naming of the fields, I'm just doing work on a friend's database

 

[boblarson]

You are working too hard at this. You can set your order by using the OrderBy of the report. You don't need to build the recordsource.

However, if you are determined to do this in this fashion, you would need to change:

RecordSource = mainsql

To

Me.RecordSource = mainsql


But I would still just use the OrderBy method as you don't need to change the recordsource of the report. Just set the recordsource without sorting in design view and then set the Orderby:

Me.OrderBy = "FieldName Desc"
Me.OrderByOn = True

and for more than one field

Me.OrderBy = "FieldName Desc, Field2, Field3 Desc"
Me.OrderByOn = True

with variables:

Me.OrderBy = Forms!YourFormName.ComboName & " Desc, " & Forms!YourFormName.ComboName2
Me.OrderByOn = True