Adding an extra choice to a ListBox

[April Marano]

Hi all,

Can I populate a ListBox (or ComboBox) with the rows drawn from a query AND
the choice "All" listed as the first row of the ListBox? If so how?

This box would allow the user to filter some data based on the row selected
or to not filter at all (at least based on that one field).

Thanks for any help in advance!

 

[April Marano]

By the way, I'm using Access 97.

 

[PC Datasheet]

Make an Union Query out of your query and another query that returns the word
"All". Look in the Help file to create the correct query containing "All".

 

[April Marano]

Great, that makes sense. Now how do I sort this new query in such a way
that "All" appears as the first row?

April

 

[PC Datasheet]

If you have no other choices at a lower alphabetical order than "All" you just
need to sort ascending. If you have lower level choices, use "<All>". Special
characters sort lower than alpha characters so "<All>" will appear first sorted
ascending.

Steve
PC Datasheet

 

[April Marano]

Thanks Steve. I'll try that.

April

 

[Graham R Seach]

I usually add a DisplayOrder column, and set the *All* column's DisplayOrder
= 0, then order the whole recordset on DisplayOrder.

SELECT Field1, Field2, 1 As DisplayOrder FROM tblMyTable
UNION SELECT "-- All --" As Field1, "" As Field2, 0 As DisplayOrder
FROM MSysObjects WHERE [Name] = "MSysDb"
ORDER BY DisplayOrder

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[PC Datasheet]

Graham,

I don't think you really need to add the DisplayOrder field. The two dashes in
front of "All" should make it first in the sort order unless you have other
selections beginning with special characters. If you try it, let me know.

Steve
PC Datasheet

I usually add a DisplayOrder column, and set the *All* column's DisplayOrder
= 0, then order the whole recordset on DisplayOrder.

SELECT Field1, Field2, 1 As DisplayOrder FROM tblMyTable
UNION SELECT "-- All --" As Field1, "" As Field2, 0 As DisplayOrder
FROM MSysObjects WHERE [Name] = "MSysDb"
ORDER BY DisplayOrder

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

If you have no other choices at a lower alphabetical order than "All" you just
need to sort ascending. If you have lower level choices, use "<All>". Special
characters sort lower than alpha characters so "<All>" will appear first sorted
ascending.

Steve
PC Datasheet