Multiple selections for criteria in query

  • Thread starter Thread starter Darrell Childress
  • Start date Start date
D

Darrell Childress

I have a table which contains a customer database. In that table are two
fields - STATE and REP. Our salesmen periodically need to view all
customers in multiple states and for a specific rep. I plan to create a
form that the salesman will open and choose his states and rep and then
get the results of the query.

My approach would be to create 4-5 fields (probably combo boxes) for the
salesman to choose a STATE and another combo box to choose the REP. I
would need to set the query to give results based on each of the combo
boxes. But if I have 4 fields on the form for state and the salesman
only wants to find customers in 2 states (he leaves 2 of the fields
bland), how do I accout for that? Or is there a better way to do this to
allow for multiple selections?
Thanks,
Darrell
 
Dear Darrell:

A more classic solution might be to use a multi-select list box. This gives
you one control in which the user may choose any number of states.

To do the query the way you request, it might be this:

SELECT *
FROM YourTable
WHERE StateName IN(Nz([Forms]![Form Name]![State1Cbo], " "),
Nz([Forms]![Form Name]![State2Cbo], " "),
Nz([Forms]![FormName]![State3Cbo], " "),
Nz([Forms]![FormName]![State4Cbo], " "),
Nz([Forms]![FormName]![State5Cbo], " "))
AND Rep = [Forms]![FormName]![RepCbo]

You would need to fix up the FormName, control names, table name, and the
columns of that table you want to see.

Tom Ellison
 
Tom,
Thanks for your response. I think you're right, this would be better
suited for a multi-select list box. Two questions:

1. Do I just set the query to look at the name of the list box on the form?

2. Instead of having to type in the 50 states for one to choose, is
there a way that I could have the list box only display the states that
are used in the table?
Thanks again,
Darrell

Tom said:
Dear Darrell:

A more classic solution might be to use a multi-select list box. This gives
you one control in which the user may choose any number of states.

To do the query the way you request, it might be this:

SELECT *
FROM YourTable
WHERE StateName IN(Nz([Forms]![Form Name]![State1Cbo], " "),
Nz([Forms]![Form Name]![State2Cbo], " "),
Nz([Forms]![FormName]![State3Cbo], " "),
Nz([Forms]![FormName]![State4Cbo], " "),
Nz([Forms]![FormName]![State5Cbo], " "))
AND Rep = [Forms]![FormName]![RepCbo]

You would need to fix up the FormName, control names, table name, and the
columns of that table you want to see.

Tom Ellison


I have a table which contains a customer database. In that table are two
fields - STATE and REP. Our salesmen periodically need to view all
customers in multiple states and for a specific rep. I plan to create a
form that the salesman will open and choose his states and rep and then get
the results of the query.

My approach would be to create 4-5 fields (probably combo boxes) for the
salesman to choose a STATE and another combo box to choose the REP. I
would need to set the query to give results based on each of the combo
boxes. But if I have 4 fields on the form for state and the salesman only
wants to find customers in 2 states (he leaves 2 of the fields bland), how
do I accout for that? Or is there a better way to do this to allow for
multiple selections?
Thanks,
Darrell
Click to expand...
Click to expand...
 
Just as an update, I used a list box and it worked fine. However, when I
changed the listbox to Multi-Select (either Simple or Extended), it no
longer works, even if I only chose one state.

Darrell said:
Tom,
Thanks for your response. I think you're right, this would be better
suited for a multi-select list box. Two questions:

1. Do I just set the query to look at the name of the list box on the form?

2. Instead of having to type in the 50 states for one to choose, is
there a way that I could have the list box only display the states that
are used in the table?
Thanks again,
Darrell

Tom said:
Dear Darrell:

A more classic solution might be to use a multi-select list box. This
gives you one control in which the user may choose any number of states.

To do the query the way you request, it might be this:

SELECT *
FROM YourTable
WHERE StateName IN(Nz([Forms]![Form Name]![State1Cbo], " "),
Nz([Forms]![Form Name]![State2Cbo], " "),
Nz([Forms]![FormName]![State3Cbo], " "),
Nz([Forms]![FormName]![State4Cbo], " "),
Nz([Forms]![FormName]![State5Cbo], " "))
AND Rep = [Forms]![FormName]![RepCbo]

You would need to fix up the FormName, control names, table name, and
the columns of that table you want to see.

Tom Ellison


I have a table which contains a customer database. In that table are
two fields - STATE and REP. Our salesmen periodically need to view
all customers in multiple states and for a specific rep. I plan to
create a form that the salesman will open and choose his states and
rep and then get the results of the query.

My approach would be to create 4-5 fields (probably combo boxes) for
the salesman to choose a STATE and another combo box to choose the
REP. I would need to set the query to give results based on each of
the combo boxes. But if I have 4 fields on the form for state and the
salesman only wants to find customers in 2 states (he leaves 2 of the
fields bland), how do I accout for that? Or is there a better way to
do this to allow for multiple selections?
Thanks,
Darrell
Click to expand...
Click to expand...
Click to expand...
 
Dear Darrell:

Answering your questions:

1. Yes

2. You could select DISTINCT from your table and do this, yes. However, if
the table is large, this would be unnecessarily slow. I recommend you have
a state table as suggested and only put the necessary states in it. You can
then also use this when selecting a state to put into the table, so users
must enter the new state into the state table first. Wouldn't this be a
best solution?

Use the SELECT DISTINCT to perform the initial insert of states. Watch out!
Some user may have ML for Maryland as well as MD. You may need to fix up
the existing table to use REAL state abbreviations and clean up this kind of
mess. That's the kind of inconsistency that this can solve.

What I do is actually more complex. I would provide the table of all 50
states, and not allow users to add them. If someone thinks ML is a state,
they will likely add it without looking to see there is already an MD. Your
database's ability to associate together all the rows for Maryland would
then be compromised. So, I would add a column for the state's full name.
In a combo box, it would show MD Maryland. Now you have something. Next, I
add a column in this state table for "active". I do allow users to activate
a state for use, just checking a check box. Only the activated states show
up in the combo box list, accomplishing what you requested, but much more
quickly. Make sense?

Tom Ellison


Darrell Childress said:
Tom,
Thanks for your response. I think you're right, this would be better
suited for a multi-select list box. Two questions:

1. Do I just set the query to look at the name of the list box on the
form?

2. Instead of having to type in the 50 states for one to choose, is there
a way that I could have the list box only display the states that are used
in the table?
Thanks again,
Darrell

Tom said:
Dear Darrell:

A more classic solution might be to use a multi-select list box. This
gives you one control in which the user may choose any number of states.

To do the query the way you request, it might be this:

SELECT *
FROM YourTable
WHERE StateName IN(Nz([Forms]![Form Name]![State1Cbo], " "),
Nz([Forms]![Form Name]![State2Cbo], " "),
Nz([Forms]![FormName]![State3Cbo], " "),
Nz([Forms]![FormName]![State4Cbo], " "),
Nz([Forms]![FormName]![State5Cbo], " "))
AND Rep = [Forms]![FormName]![RepCbo]

You would need to fix up the FormName, control names, table name, and the
columns of that table you want to see.

Tom Ellison


I have a table which contains a customer database. In that table are two
fields - STATE and REP. Our salesmen periodically need to view all
customers in multiple states and for a specific rep. I plan to create a
form that the salesman will open and choose his states and rep and then
get the results of the query.

My approach would be to create 4-5 fields (probably combo boxes) for the
salesman to choose a STATE and another combo box to choose the REP. I
would need to set the query to give results based on each of the combo
boxes. But if I have 4 fields on the form for state and the salesman only
wants to find customers in 2 states (he leaves 2 of the fields bland),
how do I accout for that? Or is there a better way to do this to allow
for multiple selections?
Thanks,
Darrell
Click to expand...
Click to expand...
Click to expand...
 
Dear Darrell:

Using a multi-select list box is a completely different animal. You'll have
to code VBA to insert the values form the list into the query. Is this the
new way you want? Yes, I did, and still do recommend it. But we're then
starting all over on how to do it.

Tom Ellison


Darrell Childress said:
Just as an update, I used a list box and it worked fine. However, when I
changed the listbox to Multi-Select (either Simple or Extended), it no
longer works, even if I only chose one state.

Darrell said:
Tom,
Thanks for your response. I think you're right, this would be better
suited for a multi-select list box. Two questions:

1. Do I just set the query to look at the name of the list box on the
form?

2. Instead of having to type in the 50 states for one to choose, is there
a way that I could have the list box only display the states that are
used in the table?
Thanks again,
Darrell

Tom said:
Dear Darrell:

A more classic solution might be to use a multi-select list box. This
gives you one control in which the user may choose any number of states.

To do the query the way you request, it might be this:

SELECT *
FROM YourTable
WHERE StateName IN(Nz([Forms]![Form Name]![State1Cbo], " "),
Nz([Forms]![Form Name]![State2Cbo], " "),
Nz([Forms]![FormName]![State3Cbo], " "),
Nz([Forms]![FormName]![State4Cbo], " "),
Nz([Forms]![FormName]![State5Cbo], " "))
AND Rep = [Forms]![FormName]![RepCbo]

You would need to fix up the FormName, control names, table name, and
the columns of that table you want to see.

Tom Ellison



I have a table which contains a customer database. In that table are
two fields - STATE and REP. Our salesmen periodically need to view all
customers in multiple states and for a specific rep. I plan to create a
form that the salesman will open and choose his states and rep and then
get the results of the query.

My approach would be to create 4-5 fields (probably combo boxes) for
the salesman to choose a STATE and another combo box to choose the REP.
I would need to set the query to give results based on each of the
combo boxes. But if I have 4 fields on the form for state and the
salesman only wants to find customers in 2 states (he leaves 2 of the
fields bland), how do I accout for that? Or is there a better way to do
this to allow for multiple selections?
Thanks,
Darrell
Click to expand...
Click to expand...
Click to expand...
 
That makes perfect sense and I think I will go with combo boxes for now.
At this point, the database is very small (less 200 records and probably
will not grow to more than 300 or so). If the need gets there, I will
readdress trying to use a multi-select list box. Thanks for your help.
Your suggestions are being put to good use.
Darrell

Tom said:
Dear Darrell:

Answering your questions:

1. Yes

2. You could select DISTINCT from your table and do this, yes. However, if
the table is large, this would be unnecessarily slow. I recommend you have
a state table as suggested and only put the necessary states in it. You can
then also use this when selecting a state to put into the table, so users
must enter the new state into the state table first. Wouldn't this be a
best solution?

Use the SELECT DISTINCT to perform the initial insert of states. Watch out!
Some user may have ML for Maryland as well as MD. You may need to fix up
the existing table to use REAL state abbreviations and clean up this kind of
mess. That's the kind of inconsistency that this can solve.

What I do is actually more complex. I would provide the table of all 50
states, and not allow users to add them. If someone thinks ML is a state,
they will likely add it without looking to see there is already an MD. Your
database's ability to associate together all the rows for Maryland would
then be compromised. So, I would add a column for the state's full name.
In a combo box, it would show MD Maryland. Now you have something. Next, I
add a column in this state table for "active". I do allow users to activate
a state for use, just checking a check box. Only the activated states show
up in the combo box list, accomplishing what you requested, but much more
quickly. Make sense?

Tom Ellison


Tom,
Thanks for your response. I think you're right, this would be better
suited for a multi-select list box. Two questions:

1. Do I just set the query to look at the name of the list box on the
form?

2. Instead of having to type in the 50 states for one to choose, is there
a way that I could have the list box only display the states that are used
in the table?
Thanks again,
Darrell

Tom said:
Dear Darrell:

A more classic solution might be to use a multi-select list box. This
gives you one control in which the user may choose any number of states.

To do the query the way you request, it might be this:

SELECT *
FROM YourTable
WHERE StateName IN(Nz([Forms]![Form Name]![State1Cbo], " "),
Nz([Forms]![Form Name]![State2Cbo], " "),
Nz([Forms]![FormName]![State3Cbo], " "),
Nz([Forms]![FormName]![State4Cbo], " "),
Nz([Forms]![FormName]![State5Cbo], " "))
AND Rep = [Forms]![FormName]![RepCbo]

You would need to fix up the FormName, control names, table name, and the
columns of that table you want to see.

Tom Ellison




I have a table which contains a customer database. In that table are two
fields - STATE and REP. Our salesmen periodically need to view all
customers in multiple states and for a specific rep. I plan to create a
form that the salesman will open and choose his states and rep and then
get the results of the query.

My approach would be to create 4-5 fields (probably combo boxes) for the
salesman to choose a STATE and another combo box to choose the REP. I
would need to set the query to give results based on each of the combo
boxes. But if I have 4 fields on the form for state and the salesman only
wants to find customers in 2 states (he leaves 2 of the fields bland),
how do I accout for that? Or is there a better way to do this to allow
for multiple selections?
Thanks,
Darrell
Click to expand...
Click to expand...
Click to expand...
 
Back
Top