Sorting

[aharmon]

I have a querie that includes name and date. The user chooses how they want to sort their report (either by name or date) on a form by selecting an option in the listbox. Here's what I want to do, but am not sure how to do it:

SELECT Name, Date
FROM tblTemp
ORDER BY
SELECT CASE [Forms]![frmClasses]!lstReportCriteria_Attended]
CASE "Name"
Name ASC
CASE "Date"
Date ASC
END SELECT

Thank you in advance for your help!!!

 

[SyedAqif]

Hi

I know my suggestion looks quite stupid but can't u build two queries.......n call them one at a time according to what type user wants n selects from the form. Just a suggestion.

Cheers!
Aqif

 

[nutshell]

aharmon,

You could use the .OrderBy property of your report to control the sorting rather than using a query. Remove the order by section from your query and attach the following code to the 'After Update' event of the option group (I have assumed its named lstReportCriteria_Attended. I have also assumed that the Name is the first option in the group and Date is the second option).

Private Sub lstReportCriteria_Attended_AfterUpdate()
  With Reports("Your report Name")
    .OrderBy = Choose(Me!lstReportCriteria_Attended, _
    "Name ASC", "Date ASC")
    .OrderOn = True
 End With
End Sub