Sort Order User Defined Via Form

[roystreet]

Hello,
I am trying to allow a user to select Ascending or Descending via a form. Currently, I have a form that allows the user to input a FY (Fiscal Year) value. The report then opens pulling only those records that meet the FY value you selected. I would like the form to allow the user to define which FY & which order they need to be in. Currently the report was sorted by "Regional_Number" This is done on the Report_Open even as below:

Me.OrderByOn = True
Me.OrderBy = "Regional_Number"

I'd appreciate any assistance. I haven't found a thread that specifically addresses allowing the user to define sort order, only sort by.

Thanks,
---roystreet

 

[Remou]

Maybe something like:

Private Sub cmdSort_Click()
Dim strSort
strSort = ""
If Trim(Me.cboSort1) & "" <> "" Then
    strSort = Me.cboSort1
    If Me.optDesc1 Then
        strSort = strSort & " Desc"
    End If
End If
If Trim(Me.cboSort2) & "" <> "" Then
    If strSort <> "" Then
        strSort = strSort & ", " & Me.cboSort2
    Else
        strSort = Me.cboSort2
    End If
    If Me.optDesc2 Then
        strSort = strSort & " Desc"
    End If
End If
    
Me.OrderBy = strSort
Me.OrderByOn = True
End Sub

 

[roystreet]

Hello,
I am trying to understand the dynamics of this, so if you don't mind - Help me, please. The form has around 8 fields that they can specify a value, or it will leave the value as "*". Once they click on "Generate Report" it opens another form that draws its data from a query. The query is set up like this example...A user can specify FY & in the query the criteris is:

"Like [Forms]![frm_AdvancedReportSystem]![cboFY]"

cboFY being the combo on the form where they specify the FY. The query does not let them choose Regional_Number, but that is what it's sorted by. I'm trying to understand where to place this code you have given me & what values to plug into it. If I have a field named cboSortBy. This field is a combo box where they can choose "Ascending" or "Descending" how would that "Plug" into this code you gave me? This will help me develop more code on my own & hopefully help someone who has the same question one day.

I may be making this much more complicated that what it really is.

Thanks, roystreet

 

[Remou]

The code snippet is based on having two combo boxes to choose sort order from, each with an option button to specify descending. I set it up this way as I was not sure from your post what you wished to do. You could add most of the code above to the Generate Report button, changing:
Me.OrderBy = strSort
Me.OrderByOn = True
To:
" ORDER BY " & strSort
Which could be added to your existing query. Alternatively, simply specify the sort order on the new form:
Forms!NewFormName.OrderBy = strSort
Forms!NewFormName.OrderByOn = True

I hope that this is in line with what you want to do.