Conditional Sort Based on User Selection

[FnclAnlyst]

Hi. I have some programming background, but am new to Access, Access SQL, and VB. I have created an Access database with a query that uses a conditional sort that I have been struggling with for what seems like eons. The sort field is based on user input on a form. I have tried a nested IIF structure and a SWITCH statement in the ORDER BY clause. I have tried using an Option Group with radio buttons to indicate the field on which to sort and have tentatively experimented with creating a TextBox which is populated through a ComboBox which has a drop down of possible sort fields. There are 11 potential sort fields. Some fields are alphanumeric while others are strictly numeric (I have double-checked data definitions, but may have missed a nuance). If I use the Option Group and list (in the SELECT statement) the alphanumeric fields first and then the numeric, the query sorts all fields as alphanumeric (e.g., 110 comes before 23). If I list the numeric fields first and then the alphanumeric, it sorts the numeric fields correctly, but gives the unhelpful “statement too complicated” error.

This is my Option Group (called SortA) code using the SWITCH.

SELECT Main.Field1, Main.Field2, Main.Field3, Main.Field4
FROM TempTable, Main
WHERE [many conditions]
ORDER BY SWITCH(
Forms!Form!SortA=1, Main.Field1,
Forms!Form!SortA=2, Main.Field2,
…;

This is my Option Group (called SortA) code using the nested IIF.

SELECT Main.Field1, Main.Field2, Main.Field3, Main.Field4
FROM TempTable, Main
WHERE [many conditions]
ORDER BY IIf(Forms!Form!SortA=1, Main.Field1,
IIf(Forms!Form!SortA=2, Main.Field2,
…;

Any suggestions would be most appreciated.

[GolferGuy]

What I have always done in this case is to build the SQL code for the query in VBA code. That way, you have access to the field name the user wants to use. I did not know that SQL in Access allowed IIf() statements in the SQL, unless it was dealing with input data to the query, that is the data portion of the query. Nor did I know that SWITCH statement could be used within the query's SQL code.

[KLMCats]

What I would suggest is defining a strOrderBy variable & set the value to a default sort string. Then go through a series of If/Else or CASE statements changing the value for strOrderBy according to the user input on the form. Then, in the section you quoted:
SELECT Main.Field1, Main.Field2, Main.Field3, Main.Field4
FROM TempTable, Main
WHERE [many conditions]
ORDER BY strOrderBy;
You could also (more verbose) set the entire SQLString repeatedly in a series of CASE statements