Listbox- Generate query using pre-specified multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shmoussa - 21 May 2007 21:52 GMT

I'm not sure how to do the following. I have a single-selection
listbox. When I select a certain option and click a command button, I
want pre-specified multiple criteria to be used to generate a query.

Example: If I select "Light colors" from my listbox, and then click a
command button I want a query to generate using "blue OR yellow" as
the criteria. If I select "dark colors" from the listbox and click the
command box, the query should generate using "brown OR black" as the
criteria.

The idea (and I know this is completely wrong, however I brandnew to
VB, this is just so you get the idea)

If listbox.ItemsSelected="Light Colors" Then
strSQL = "SELECT Colors, Price " & _
"FROM [Clothing] " & _
"WHERE Colors="blue OR yellow""
Else If
If listbox.ItemsSelected="DarkColors" Then
strSQL = "SELECT Colors, Price " & _
"FROM [Clothing] " & _
"WHERE Colors="brown OR black""

Once again, I know this code is wrong- but this is idea I am going
for. Please let me know if and how this could be done.

Reply to this Message

Douglas J. Steele - 21 May 2007 22:04 GMT

You can't use OR like that.

strSQL = "SELECT Colors, Price " & _
"FROM [Clothing] " & _
"WHERE Colors=""blue"" OR Colors=""yellow"""

or

strSQL = "SELECT Colors, Price " & _
"FROM [Clothing] " & _
"WHERE Colors='blue' OR Colors='yellow'"

or

strSQL = "SELECT Colors, Price " & _
"FROM [Clothing] " & _
"WHERE Colors IN (""blue"", ""yellow"")"

or

strSQL = "SELECT Colors, Price " & _
"FROM [Clothing] " & _
"WHERE Colors IN ('blue', 'yellow')"

Note the "extra" quotes I had to insert.

As well, ItemsSelected actually returns a collection of all of the selected
items, and so can't be used in the way you're trying to.

Assuming your list box is a simple one, with MultiSelect set to none, you'd
simply use:

Select Case Me.MyListbox
Case "Light Colors"
strSQL = "SELECT Colors, Price " & _
"FROM [Clothing] " & _
"WHERE Colors IN ('blue', 'yellow')"
Case "Dark Colors"
strSQL = "SELECT Colors, Price " & _
"FROM [Clothing] " & _
"WHERE Colors IN ('brown', 'black')"
End Select

(where "MyListbox" is the name of the listbox)

If the MultiSelect property is set to Simple or Extended, then you have to
determine the membership of the collection, even if only a single entry
happens to be selected. However, since that doesn't make sense for your
example, I'll leave that code out.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I'm not sure how to do the following. I have a single-selection
> listbox. When I select a certain option and click a command button, I
[quoted text clipped - 21 lines]
> Once again, I know this code is wrong- but this is idea I am going
> for. Please let me know if and how this could be done.

Reply to this Message

shmoussa - 22 May 2007 16:40 GMT

Thank you so much. I got it to work the way I want it to. I appreciate
it. I'm posting another question about a multiselect box as a new
topic. I'd once again appreciate your assistance. Thank you again.

Reply to this Message