Sort Access Query From Variable
I wanted to see if there was a way to sort an Access query based off a variable. I have a textbox on a form and I want to be able to type in a field name and then open one query and it's sorted by the field name I type in the text box. Any help would be greatly appreciated.
Software/Hardware used:
Software/Hardware used:
ASKED:
November 18, 2008 9:12 PM
UPDATED:
November 19, 2008 9:30 PM
RELATED QUESTIONS
Answer Wiki:
There are a couple of ways you could do this. One is in VBA and the other is in the query itself using multiple IIF statements.
The query:
In your query, make a column called SortBy. Assuming your form name is MyForm and the field to sort by is SortBy, the Field property of the query would be: SortBy: IIf(forms!MyForm!SortBy="Name",MyTable.Name,IIF(forms!MyForm!SortBy="ZipCode",MyTable.ZipCode,iif(forms!MyForm!SortBy="State",MyTable.State))) - I just did a sample of three columns; but you can add more. Make sure you indicate how you want it to sort.
VBA:
If you have a print button on the form that runs the query, you can have the following code: (if you don't have a button, you could put the code in the after_update event of the SortBy field.
Private Sub Print_Click()
Dim SQL As string
SQL = "Select * From MyTable Order By " & Me!SortBy & ";" 'Build the query with the selected SortBy
on error resume next 'if the query doesn't exist, don't worry about it
DoCmd.DeleteObject acQuery, "MyQuery" 'Deletes the query first
on error goto 0 'resume error checking
Currentdb().CreateQueryDef ("MyQuery",SQL) 'Build the query
docmd.openquery "MyQuery"
End Sub
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks for the response, that worked except for now it’s sorting number fields alphabetically instead of numerical. I tried to use a CDbl() on the number fields but that doesn’t work.
Dustin
I found that I had to use 2 sort columns one for numbers and one for text. Thanks for your help again.
Dustin