Choosing a Sort Field for Report dynamically

[bb3261]

I need to sort a report based on a field the user inputs and I can't seem to find an easy way to do it. I tried making the ORDER BY clause of the query a parameter but Access didn't like that. I tried building the entire query in code and then basing the report on a form field which contained the string for the query, but it woudln't recognize it. Since that seems to be the most likely solution, though, how do I base a report on a query built in code?

 

[Baldrick]

Have you tried adding a group level in your report?

Click on the Grouping/Sorting button (in design mode) and just type in the parameter (i.e. DATE) in the Field/Expression. If you want visible headers/footers make sure you select "Yes". Treat it as if it were a regular field. Close that...now run your report. Access will prompt you for your value.

Not sure if this is what you're looking to do, though.

 

[bb3261]

Thanks for the response. I understand what you're saying, but I wouldn't trust the user to be able to type in the sort field. I have a form where they select the field on which to sort in a combo box, and then I want to open the report sorted by that field. Could I use your technique and have the parameter in the Grouping/Sorting box refer to a text box on the input form (e.g. Forms![Input Form]![SortField])? If not, can I build the query in VB code and open the report using the query?

[This message has been edited by bb3261 (edited 02-27-2002).]