DIsplay order change (up/downP select box)

P

Phil Smith

I have a query. I want the display order of the query, to be user
seledtable.

For instance, am Ice cream query might have "Cone Type", "Flavor", and
"Brand."

I want to see "Cone Type", "flavor" and "Brand", select one, and hit an
up and down button, and change the order, so that the query will display
Brand first, then, Flavor, as I chose.

I think I could figure it out, I assume the result will go into an SQL
string, and I can figure that out, but there has to be something out
there...

I have no idea what search terms to use to even look.

Thanx
 
D

Dale Fye

where do you want to use this (listbox, combo box, continuous form, datasheet)?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
P

Phil Smith

Once I have the proper order selected, the result would drive a query
which will be exported to Excel.





Dale Fye wrote:
 
D

Dale Fye

so you don't intend to actually display the results of this process in a form
or report.

What I would recommend is creating the basic query (without any sorting) and
saving it.

Then, on a form, I'd provide the user with a list (lst_QueryFields) of the
fields that they can sort by (do this in a list and set the listboxes
RowSourceType to "Field List", and set the RowSource to the name of the
query. This will give them the opportunity to select which field they want
to sort by. Then, add a combo box (cbo_SortDirection) with the options
"Ascending" and "Descending". Finally, add a button that will export the
query to Excel. In the click event of this button, modify the SQL of your
query, so that it removes any previous order by clause, and then appends your
new clause, something like:

Private sub cmd_Export_Click

Dim strSQL as string
Dim intCharPos as integer

strSQL = currentdb.querydefs("yourQuery").sql
intCharPos = instr(strSQL, "ORDER BY")
if intCharPos > 0 then strSQL = left(strSQL, intCharPos - 1)

'Check to see whether a sort field was selected
IF not isnull(me.lst_QueryFields) then
strSQL = strSQL & " ORDER BY " & me.lst_QueryFields
'Check to see whether the order is Ascending or Descending.
'If descending, add that to the ORDER BY clause
IF NZ(me.cbo_SortDirection, "Ascending") = "Descending" then
strSQL = strSQL & "[DESC]"
end if
currentdb.querydefs("yourQuery").SQL = strSQL

end if

docmd.OutputTo acOutputQuery,"yourQuery",acformatXLS

End Sub

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top