Sort Access Query From Variable

390 pts.
Tags:
Access query
Microsoft Access
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • RoadDust
    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
    390 pointsBadges:
    report
  • RoadDust
    I found that I had to use 2 sort columns one for numbers and one for text. Thanks for your help again. Dustin
    390 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following