Sort By Value Of Combo Box

[mrssevans]

I am using a form to allow the user to choose the sort and filter of the query. I have seven different fields that I need to allow them to sort by and I also need to allow them to pick the order in which they sort. This is the "Order By" code I am using but it is not working...can anyone fix this for me?
"Carcass Filter" is form name.
"Combo32" is the first combo box I have that allows them to choose the first field to sort by.

ORDER BY IIf(FORMS.[CARCASS FILTER].OPTION1=-1,FORMS.[CARCASS FILTER].COMBO32);

 

[GumbyD]

Your ORDER BY clause needs to have tablename.Fieldname. I am guessing that you are just returning the field name. Try this:

ORDER BY IIf(FORMS.[CARCASS FILTER].OPTION1=-1,"Tablename." & cstr(FORMS.[CARCASS FILTER].COMBO32),"");

GumbyD

 

[mrssevans]

I don't have a table name to put in there. This filter form is using unbound combo boxes. I have simply typed the values in that match the names of the fields that I would like to sort. Is there a better way I should do this? I am very open to suggestions at this point.

I tried putting in the the query that I am querying rather than a table, but it still didn't work.

 

[GumbyD]

Sorry Sam I had left for the day. I played around with this and have a working solution, but it is not pretty. You can build into your ORDER BY clause an iif statement that checks to see if they want to sort and then checks the value in the combo box and sets the value to the field. Here is an example that I tried to use your naming conventions (of course I did not know the names of the fields in the combo to choose from so you will need to change them to make it work for you)

ORDER BY IIf([FORMS]![CARCASS FILTER]![OPTION1]=-1,IIf([Forms]![CARCASS FILTER]![Combo32]="Field1",[field1],IIf([Forms]![CARCASS FILTER]![Combo32]="Field2",[field2],"")));

I hope that makes sense.

GumbyD

 

[mrssevans]

It makes complete sense. Thank you so much for the help and I will give that a try right now. Thanks again!!!