Filtering a Query with List Box

S

SCHNYDES

Hi all,

Having a little trouble filtering my query with list box selections.

I have 4 list boxes, each with different choices. My query has the fields
in it's detail related to the list boxes.

In my criteria for each field related to the list box in my query I hjave
the following:
[Forms]![MyForm]![ListBox]

However whenever running the query I get no returns. What am I doing wrong?
Is there a way to make it so when no selections are chosen, the query runs
and returns all rows? Or if one of the selections is not chosen for a
certain list box, it returns all rows and includes the other criteria?

Hope I didn't confuse anyone, appreciate the help.
 
R

Robert_DubYa

Try this:

like "*" & [Forms]![[Myform]![listbox]

the problem will be that you are using a wildcard. If your listbox contains
is a value that is also part of another value you will get that value as well.

example:

list box values
1
11
21
31

If your user choose 1 they will get anthing that ends with one. The placing
of the wildcard could be at the end as well

Another thought is to use * as your default value and just use:

like [Forms]![[Myform]![listbox]

hope this helps.
 
T

Tom Wickerath

Hi Schnydes,

Without testing, I would expect your criteria to work only with the
multiselect property set to None for all of the listboxes, so you might as
well use a combo box to save screen real estate. If you want multiselect
capability, you need to use VBA code to iterate through the selected items
and build the WHERE clause of the SQL statement on-the-fly.
Is there a way to make it so when no selections are chosen, the query runs
and returns all rows?
Click to expand...
With the VBA method, where you iterate the items selected, you simply would
not include any criteria in the WHERE portion of your SQL statement, if no
selections were made. With a combo box, or listbox with the multiselect
property set to None, try the following:

Like [Forms]![MyForm]![ListBox] OR Is Null


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi all,

Having a little trouble filtering my query with list box selections.

I have 4 list boxes, each with different choices. My query has the fields
in it's detail related to the list boxes.

In my criteria for each field related to the list box in my query I have
the following:
[Forms]![MyForm]![ListBox]

However whenever running the query I get no returns. What am I doing wrong?
Is there a way to make it so when no selections are chosen, the query runs
and returns all rows? Or if one of the selections is not chosen for a
certain list box, it returns all rows and includes the other criteria?

Hope I didn't confuse anyone, appreciate the help.
 
S

SCHNYDES

Yeah, I wanted to stay away from a combo box and make it unique to the users.
I'll give Robert's idea a try and then move to the combo box if its still
not working for me, thanks for the help.


Tom Wickerath said:
Hi Schnydes,

Without testing, I would expect your criteria to work only with the
multiselect property set to None for all of the listboxes, so you might as
well use a combo box to save screen real estate. If you want multiselect
capability, you need to use VBA code to iterate through the selected items
and build the WHERE clause of the SQL statement on-the-fly.
Is there a way to make it so when no selections are chosen, the query runs
and returns all rows?
Click to expand...
With the VBA method, where you iterate the items selected, you simply would
not include any criteria in the WHERE portion of your SQL statement, if no
selections were made. With a combo box, or listbox with the multiselect
property set to None, try the following:

Like [Forms]![MyForm]![ListBox] OR Is Null


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi all,

Having a little trouble filtering my query with list box selections.

I have 4 list boxes, each with different choices. My query has the fields
in it's detail related to the list boxes.

In my criteria for each field related to the list box in my query I have
the following:
[Forms]![MyForm]![ListBox]

However whenever running the query I get no returns. What am I doing wrong?
Is there a way to make it so when no selections are chosen, the query runs
and returns all rows? Or if one of the selections is not chosen for a
certain list box, it returns all rows and includes the other criteria?

Hope I didn't confuse anyone, appreciate the help.
Click to expand...
 
T

Tom Wickerath

If you are comfortable using VBA, I can send you a sample database that
includes a QBF (Query by Form) with a multiselect listbox. A "munged" form of
my e-mail address is shown below, if you are interested. If you'd like this
sample, send me a private e-mail message with a valid reply to address.
Whatever you do, do not post your real, unaltered, e-mail address to any
newsgroup message, unless you love receiving the attention of spammers.


Tom
QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Yeah, I wanted to stay away from a combo box and make it unique to the users.
I'll give Robert's idea a try and then move to the combo box if its still
not working for me, thanks for the help.
__________________________________________

:

Hi Schnydes,

Without testing, I would expect your criteria to work only with the
multiselect property set to None for all of the listboxes, so you might as
well use a combo box to save screen real estate. If you want multiselect
capability, you need to use VBA code to iterate through the selected items
and build the WHERE clause of the SQL statement on-the-fly.
Is there a way to make it so when no selections are chosen, the query runs
and returns all rows?
Click to expand...
With the VBA method, where you iterate the items selected, you simply would
not include any criteria in the WHERE portion of your SQL statement, if no
selections were made. With a combo box, or listbox with the multiselect
property set to None, try the following:

Like [Forms]![MyForm]![ListBox] OR Is Null


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi all,

Having a little trouble filtering my query with list box selections.

I have 4 list boxes, each with different choices. My query has the fields
in it's detail related to the list boxes.

In my criteria for each field related to the list box in my query I have
the following:
[Forms]![MyForm]![ListBox]

However whenever running the query I get no returns. What am I doing wrong?
Is there a way to make it so when no selections are chosen, the query runs
and returns all rows? Or if one of the selections is not chosen for a
certain list box, it returns all rows and includes the other criteria?

Hope I didn't confuse anyone, appreciate the help.
 
Top