Sorting Problems

[AndyCabbages]

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

 

[pbaldy]

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

 

[AndyCabbages]

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

 

[DCrake]

Can the user select more than one sort order?

 

[AndyCabbages]

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

 

[DCrake]

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?

 

[AndyCabbages]

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

 

[DCrake]

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

 

[AndyCabbages]

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:

How would I impliment that? Just add another IF statement within the current IF statement?

 

[DCrake]

What is appearing in your combo boxes? Field names?

 

[AndyCabbages]

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

 

[AndyCabbages]

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

 

[DCrake]

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

 

[AndyCabbages]

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

 

[DCrake]

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

 

[AndyCabbages]

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?