Sort listbox content

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good afternoon,

I have used the open event od a form to populate a 2 column listbox from a
table based on selected criteria. My issue is that the data comes back in a
very messy order which makes it difficult to review. How can I sort the data
ascending on the first column and descending on the second?

Thank you!

Daniel
 
Daniel said:
Good afternoon,

I have used the open event od a form to populate a 2 column listbox
from a table based on selected criteria. My issue is that the data
comes back in a very messy order which makes it difficult to review.
How can I sort the data ascending on the first column and descending
on the second?
Click to expand...

If your list box's RowSource is a query expressed as a SQL statement,
you can specify the sort order you want via the ORDER BY clause of the
query.
 
Good afternoon,

I have used the open event od a form to populate a 2 column listbox from a
table based on selected criteria. My issue is that the data comes back in a
very messy order which makes it difficult to review. How can I sort the data
ascending on the first column and descending on the second?

Thank you!

Daniel
Click to expand...

Use a query as List Box Rowsource.
In the query, sort the first column Ascending. Sort the second column
DESC.
 
In form design view, select the list box. Open the properties dialog and
double click on the rowsource property. It will open the query builder.
Select your columns and in the first column's Sort row, select Ascending.
Select Descending for the second column. Close the query builder. Answer
yes to the prompt. Now in the row source, you will see the SQL that will be
used as the row source which will give you the sorting you need.
 
Dirk,

In this case my row source is not a query or table so I cannot use the query
builders with the sort option. My listbox is populated on the fly through
coding?!

Is there a solution? I saw someone mention populating a table with the
values and then using it as a row source but I don't like that idea too
much?!!!

Thank you

Daniel
 
Daniel said:
Dirk,

In this case my row source is not a query or table so I cannot use
the query builders with the sort option. My listbox is populated on
the fly through coding?!

Is there a solution? I saw someone mention populating a table with
the values and then using it as a row source but I don't like that
idea too much?!!!
Click to expand...

So your listbox's RowSource is a value list? Or are you using a custom
RowSourceType function? I'm guessing it's the former.

Unless you want to recreate the value list every time the sort order
changes -- with whatever underlying sort mechanism you might use -- I
think you would do much better to use a query as your rowsource. That
way you can sort the values any way you want, just by changing the ORDER
BY clause of the query. Ideally, you would not use a table that is
dedicated to be the rowsource for this list box, but rather would query
some existing table or tables for the list items.

Where do the values for this list box come from, anyway? You're working
in a database, so it makes sense that most data would ultimately be
stored in tables, from which you can extract what you want by way of
queries. You haven't told us any details about why you are calculating
the list items on the fly, so I don't know if that broad generalization
is out of line, but it sure seems you're doing something unusual.
 
So your list box row source is a Value list you create, or an SQL statment?
If you are constructing an SQL statement, include the ORDER BY. I suspect
it is probably a Value list. One way would be to put each element of your
value list is an array and sort it. Another would be to modify your code to
construct it in the correct order. Using a table to put the values in and
using a query is another. Or, you can use an ADO recordset you create at
runtime which lives only in memory but can be manipulated like a table.

If none of those options are appealing to you, explain to your users you
have reordered the alphabet and they will have to learn the new order :)
 
Dirk,

I believe you might be right about me taking the wrong route to achieve my
desired result. I will try to make a query/series of queries to be used to
populate the list box!

I'll be in touch if I have a problem.

Thank you (all of you) for your help and insight!

Daniel
 
Daniel said:
Dirk,

I believe you might be right about me taking the wrong route to
achieve my desired result. I will try to make a query/series of
queries to be used to populate the list box!

I'll be in touch if I have a problem.

Thank you (all of you) for your help and insight!
Click to expand...

Good luck, and by all means postback if you run into problems.
 
Back
Top