Mary:
Below is some code that shows one alternative for accomplishing this task.
It assumes that you have a form with a text box and a button. The code is
designed to work in the button click event.
The unsorted query is the base query without any ORDER BY clause. You will
need to create the sorted query, although it will be overwritten each time
the event procedure is run. All the code does is take the SQL from the
unsorted query and append an ORDER BY clause based on the field name of the
textbox. You could also use a combo box (instead of the textbox) and fill
it with the field names in the table by using the Fields collection of the
TableDef object. You may also want to add code to check for a null or empty
string in the textbox. This is where using a combo box would be
advantageous.
Dim db As Database
Dim qdfUnsorted As QueryDef
Dim qdfSorted As QueryDef
Set db = CurrentDb
Set qdfUnsorted = db.QueryDefs("MyUnsortedQuery")
Set qdfSorted = db.QueryDefs("MySortedQuery")
qdfSorted.SQL = Replace(qdfUnsorted.SQL, ";", " ORDER BY " &
Forms!Form1!txtMyFieldName & ";")
DoCmd.OpenQuery qdfSorted.Name
Set db = Nothing
Set qdfUnsorted = Nothing
Set qdfSorted = Nothing
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com
This response is supplied "as is" without any representations or warranties.
Is it possible to use a form to input a field name and then sort records
based on that field?
