list box sort

  • Thread starter Thread starter smk23
  • Start date Start date
S

smk23

I have a list box that uses a query for the rowsource. How would I change the
sort order (on click of command button):
I thought of

Dim strSQL as string

strSQL = CurrentDb.QueryDefs("pqSearchList").SQL & _
" ORDER BY NameLFMN"

And then assign strSQL as the new query def, but that get's into removing
the ";" etc. Is there a simpler way?

Thanks for your time!!
Sam
 
Copy the SQL statement from pqSearchList into your code, and chop of the
ORDER BY clause. Define a string to set up with the ORDER BY clause. Assign
the result to the list box's RowSource:

Dim strOrderBy As String
Const strcStub = "SELECT Table1.ID, Table1.SomeField FROM Table1 "
strOrderBy = "ORDER BY NameLFMN;"
Me.List0.RowSource = strcStub & strOrderBy
 
Allen,
pqSearchList gets redefined according to what I'm searching for, so it
wouldn't be practical to make a constant for every possibility. I guess I
will have to trim the ";" at the end of the statement, but I don't like that
because it's asking for trouble, i.e. an extra space after the ";" or similar.
 
You can use InstrRev() to locate the ";" at the end, and also the "ORDER BY"
(Typically is is followed by a CR LF.)

Parsing a SQL statement is not a simple task. There may be uilities to do
it, but there are actually lots of possiblities to handle, such as
subqueries, UNION, action queries, crosstab, parameters, literals that
contain reserved words, and so on.

If you are already modifying the query, then building the entire SQL
statement as a string in code might be even easier.

Another possibility might be to leave the existing query intact:
Me.List0.RowSource = "SELECT * FROM pgSearchList ORDER BY NameLFMN;"
 
If it was just the semi-colon, you could easily use the Replace function:

strSQL = Replace(CurrentDb.QueryDefs("pqSearchList").SQL, ";", "") & _
" ORDER BY NameLFMN"

but what are you going to do the next time, when the ORDER BY NameLFMN is in
the SQL as well? If you're saying that you're saving the SQL in another
query, and that pqSearchList is never changed, how's that any better than
having the base SQL assigned to a constant, like Allen's suggesting?
 
You're right, Doug.
What I'm doing is: pqSearchList is a pass-through to a stored procedure
which is just a SELECT statement. I can use pqSearchList as the source for
another query, qrySearchListNameOrder, and just change the rowsource of the
list box to that when I want that sort.

Thanks for the help to both!!
Sam
 
Back
Top