This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(310.4, 34%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Original post I made but technically a different question.
https://learn.microsoft.com/en-us/answers/questions/652130/access-database-dynamic-sort-query.html
With the below code in an SQL query I can use a tempvar to identify the proper ORDER BY criteria. But my new problem is, what if I want to sort by DESC? I have been unable to get this to work dynamically below:
ORDER BY IIf([TempVars]![tpvFavoriteList1]="TitleAsc", [qryArchive].[ObjectName] & ASC, [qryArchive].[ObjectName] & DESC);
It treats the ASC and DESC as parameters that I have to type with a prompt and it doesnt matter anyways because it treats it as a string value. How can I dynamically add the ASC and DESC?
An alternative solution would be to assign an SQL statement to the control's RowSource property in code in the form's module, e.g. in its Open event procedure, e.g.
Me.lstContacts.RowSource = _
"SELECT ContactID, FirstName & "" "" & LastName " & _
"FROM Contacts " & _
"ORDER BY LastName " & IIF([TempVars]![NameOfVariable]="TitleAsc"
, "ASC", "DESC")
Set the list box's ColumnWidths property to 0 to hide the fist column.
Thank you for the much improved, efficient approach! I will use the SQL expression in the RowSource property and update via VBA. Allows me to use 1 query to do it all. Sample code logic below:
If TempVars!tpvFavoriteList1 = "TitleASC" Then
Forms!frmNavigator!FavoriteList.RowSource = "SELECT * FROM qryNavFavorite ORDER BY ObjectName ASC;"
ElseIf TempVars!tpvFavoriteList1 = "TitleDES" Then
Forms!frmNavigator!FavoriteList.RowSource = "SELECT * FROM qryNavFavorite ORDER BY ObjectName DESC;"
ElseIf TempVars!tpvFavoriteList1 = "DateASC" Then
Forms!frmNavigator!FavoriteList.RowSource = "SELECT * FROM qryNavFavorite ORDER BY TimeStamp ASC;"
ElseIf TempVars!tpvFavoriteList1 = "DateDES" Then
Forms!frmNavigator!FavoriteList.RowSource = "SELECT * FROM qryNavFavorite ORDER BY TimeStamp DESC;"
End If
You can't. Just like field names and table names, this must be literal.
I was forced to create two queries to address this issue. One is for ASC and the other for DESC sorting. Other than that, I can use the IFF() to sort by any other fields dynamically. Used VBA to switch between queries in a listbox. It accomplishes what I was looking for but the code is very cumbersome.