Sorting Query by Combobox value on form (varying fields)

[automationamateur]

I have a report that is fed by 'query123', and kicked off by 'form123'. I want to sort the report 4 different ways (individually not at the same time): 1. Client Name, 2. Accounts On file, 3. Margin %, and 4. Start Date. All of these sort options are separate fields in 'query123'.


I know there are a few options I've tried all with no success. 1. Sort the 'query123' data that feeds the report based on a combobox on 'form123'. This is easy enough if I want to sort on one field consistently but I need to be able to sort 1 of 4 fields based on user selection on form combobox. 2. Sort report 'On Load' event that sorts report based on selection in combobox on 'form123'. This only fired the form twice which doubled the time it took to run and it still didn't sort correctly. 3. Placed listbox on form itself and used an ******* event to resort the report based on user selection in the listbox. I couldn't get this to work either.

My ideal solution would be the form combobox to feed the query a sort field and then the data will feed report already sorted. If I need to resort I will rerun from the form and change combobox selection.

I've spent roughly 8 hours on this to no avail. Can anyone help me out here? I've tried every suggestion I could find on the net. If this even possible if my sort fields are 4 different fields? Thanks for the help in advance.

 

[jackd]

Reports have their own sorting and grouping - anything you do there could override things you do in the query.

If you initiate your sort from the form and and use the form selection as a query constraint, and use that query as recordsource for the report, do you still have an issue?

 

[automationamateur]

The end goal is for my sorting to be dynamic based on user choice so I dont to do static sorting through the form. It's been my experience that a report, if it doesn't have any of its own sorting, will display query data as it is sorted in the query itself.

I have tried your suggestion, initiating the sort from form, using the selection as a query constraint and yes my report's recordsource is the query. I have lots of experience doing this when I am only sorting one field in the query and it usually works. However I want to use one form combobox that will sort 1 of 4 fields in the query. How would I put 4 sorting contraints in the query, either in design view or sql in the 4 various fields. Does this make sense? Thanks for the help thus far.

If combobox value = 1 sort field 1 ascending *each of these fields is a different field in query.
if combobox value = 2 sort field 2 ascending
if combobox value = 3 sort field 3 ascending
if combobox value = 4 sort field 4 ascending

 

[jackd]

I think that approach should work.

If your Select is the same each time

eg

SELECT field1,field2,field3,field4,.....from YourTable
Order by 1

SELECT field1,field2,field3,field4,.....from YourTable
Order by 2

 

[automationamateur]

I'm not sure what you mean? What would the sql look like for the 4 different order by's in my query. Does it matter that my form's record source isn't tied to this query? I feel like I'm a little lost but maybe I'm making this more difficult than it needs to be. Thanks for the help.