I've been searching forums for hours and trying different variations of functions to no avail...
I'm trying to sort a query on two fields based on a parameter selection from another query. These two fields change based on this parameter. I tried manipulating the Order By SQL statement using the Choose function (based on the "index" number returned by qryRange.Division). It looked something like this:
Order By Choose([qryRange.Division], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Department, Element.Program], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Department, Element.Program], [Element.Program, Element.Brand], [Element.Program, Element.Brand]);
where indexes 4 and 7 return a sort using Department/Program and the remaining indexes return a sort using Program/Brand.
When attempting to run the query I received an error message stating that the query in which I added the choose statement is invalid due to a character (or something to that effect). I futzed around with the parantheses and brackets and received other errors having to do with commas, etc... Just couldn't get it to work.
I then attempted to nest two Iif functions after the Order By statement. Translated into English it said, if qryRange.Division = 4 or 7 then [Element.Department, Element.Program], otherwise [Element.Program, Element.Brand]. This also did not work.
Please help. Let me know if you need to see any data/SQL.