Multiple Selection per field Query

[Confused]

I want to run a query on a form that is bound to a Customers Table. Combo box
doesn't seem to do it. How do I select multiple criteria for the same
field. E.g., Show me the customers that are in St Louis, Miami, and
Omaha-Along with the customer that order product X and Z. etc. Is this
possible?

I then want to email the customers as a result of the query through the
Contacts Table.

 

[Graham Mandeno]

Hi Confused

You need to put a multi-select listbox on your form containing all the
possible values that can be selected. Then, when the time comes to apply
your filter, you create a filter string from the selected values in your
listbox.

For example, you have a multiselect listbox named lstSelectCities with this
RowSource:
SELECT distinct City from Customers;
This will list all the possible cities - only one instance of each.

Now, you need to make a comma-separated list of the selected cities. You
can do that like this:

Dim vItem As Variant, sList As String
Const DQ = """" ' one double-quote
With lstSelectCities
For Each vItem In .ItemsSelected
sList = sList & DQ & .ItemData(vItem) & DQ & ","
Next
sList= Left(sList, Len(sList) - 1) ' remove last comma
End If
End With

This will give you, for example: "St Louis","Miami","Omaha"

You can use the string you have constructed here to form part of your WHERE
condition or filter string:

City In ("St Louis","Miami","Omaha")

Do a similar thing with your products. Here, you will probably want to use
a subquery to select the customers who have ordered a product:

CustomerID In (Select Order.Customer from Orders inner join OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where OrderItems.ProductID In
(5,42,99))

The part that comes from your listbox is the three ProductIDs that have been
selected:
5,42,99

Then put all of these WHERE fragments together:

SELECT * from Customers
WHERE (City In ("St Louis","Miami","Omaha"))
AND (CustomerID In (Select Order.Customer from Orders inner join OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where OrderItems.ProductID In
(5,42,99)))

 

[Confused]

Thank you very much for replying. I put a list box using the toolbox and
selected the fields. It then just lists all of the entries in the table for
those fields. Am I starting off right? Will I be selecting the query off
the list box itself or through applying the code. Don't know too much about
programming, butI think I can manage if I get a bigger picture?

Hi Confused

You need to put a multi-select listbox on your form containing all the
possible values that can be selected. Then, when the time comes to apply
your filter, you create a filter string from the selected values in your
listbox.

For example, you have a multiselect listbox named lstSelectCities with this
RowSource:
SELECT distinct City from Customers;
This will list all the possible cities - only one instance of each.

Now, you need to make a comma-separated list of the selected cities. You
can do that like this:

Dim vItem As Variant, sList As String
Const DQ = """" ' one double-quote
With lstSelectCities
For Each vItem In .ItemsSelected
sList = sList & DQ & .ItemData(vItem) & DQ & ","
Next
sList= Left(sList, Len(sList) - 1) ' remove last comma
End If
End With

This will give you, for example: "St Louis","Miami","Omaha"

You can use the string you have constructed here to form part of your WHERE
condition or filter string:

City In ("St Louis","Miami","Omaha")

Do a similar thing with your products. Here, you will probably want to use
a subquery to select the customers who have ordered a product:

CustomerID In (Select Order.Customer from Orders inner join OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where OrderItems.ProductID In
(5,42,99))

The part that comes from your listbox is the three ProductIDs that have been
selected:
5,42,99

Then put all of these WHERE fragments together:

SELECT * from Customers
WHERE (City In ("St Louis","Miami","Omaha"))
AND (CustomerID In (Select Order.Customer from Orders inner join OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where OrderItems.ProductID In
(5,42,99)))

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I want to run a query on a form that is bound to a Customers Table. Combo
box
doesn't seem to do it. How do I select multiple criteria for the same
field. E.g., Show me the customers that are in St Louis, Miami, and
Omaha-Along with the customer that order product X and Z. etc. Is this
possible?

I then want to email the customers as a result of the query through the
Contacts Table.

 

[Graham Mandeno]

Hi Confused (still?

Just try it with one listbox first, to select multiple cities. Do you have
a separate table for cities, or is it just a text field in your Customers
table? If it's the latter, then you must include the DISTINCT predicate in
your listbox RowSource so that you get each city listed only once.

The trick is to just take it one step at a time if you are unsure.

First create the listbox - it should list each city only once, and you
should be able to select multiple cities. Set MultiSelect to "Simple" and
make sure ControlSource is blank.

Next you can create a command button and attach the code below to its Click
event. Note that you can use Debug.Print and MsgBox to show you how things
are progressing. For example, after each
sList = ...
in your code, you might put
Debug.Print sList
so you can see that the string is being built correctly.

Then you can try including that comma-separated string as part of a WHERE
clause and using it to filter your report.

When all that is working, you can add further listboxes to filter other
fields.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you very much for replying. I put a list box using the toolbox and
selected the fields. It then just lists all of the entries in the table
for
those fields. Am I starting off right? Will I be selecting the query off
the list box itself or through applying the code. Don't know too much
about
programming, butI think I can manage if I get a bigger picture?

Hi Confused

You need to put a multi-select listbox on your form containing all the
possible values that can be selected. Then, when the time comes to apply
your filter, you create a filter string from the selected values in your
listbox.

For example, you have a multiselect listbox named lstSelectCities with
this
RowSource:
SELECT distinct City from Customers;
This will list all the possible cities - only one instance of each.

Now, you need to make a comma-separated list of the selected cities. You
can do that like this:

Dim vItem As Variant, sList As String
Const DQ = """" ' one double-quote
With lstSelectCities
For Each vItem In .ItemsSelected
sList = sList & DQ & .ItemData(vItem) & DQ & ","
Next
sList= Left(sList, Len(sList) - 1) ' remove last comma
End If
End With

This will give you, for example: "St Louis","Miami","Omaha"

You can use the string you have constructed here to form part of your
WHERE
condition or filter string:

City In ("St Louis","Miami","Omaha")

Do a similar thing with your products. Here, you will probably want to
use
a subquery to select the customers who have ordered a product:

CustomerID In (Select Order.Customer from Orders inner join
OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where OrderItems.ProductID
In
(5,42,99))

The part that comes from your listbox is the three ProductIDs that have
been
selected:
5,42,99

Then put all of these WHERE fragments together:

SELECT * from Customers
WHERE (City In ("St Louis","Miami","Omaha"))
AND (CustomerID In (Select Order.Customer from Orders inner join
OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where OrderItems.ProductID
In
(5,42,99)))

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I want to run a query on a form that is bound to a Customers Table.
Combo
box
doesn't seem to do it. How do I select multiple criteria for the same
field. E.g., Show me the customers that are in St Louis, Miami, and
Omaha-Along with the customer that order product X and Z. etc. Is
this
possible?

I then want to email the customers as a result of the query through the
Contacts Table.

 

[Confused]

OK... When I create the list box I'm using the wizard. I don't see an option
for a multiselect list box. There are three options. I select the third one
to look up values in a table or query. It then puts this statement on the
Row Source SELECT [customers].[ID], [customers].[Field3] FROM [customers];

Field 3 is where the list of cities are. If I change that to Select
Distinct it errors out or doesn't list anything. I could go in and manually
type each city if I select option2. But what I'm still "confused" about is
once I get the list box to list all of the cities, how do you select multiple
ones? Because I can still only make one selection. So maybe the questions
should have been first, How do I build a "multiselect list box? " Thanks
for your patience.

Hi Confused (still?

Just try it with one listbox first, to select multiple cities. Do you have
a separate table for cities, or is it just a text field in your Customers
table? If it's the latter, then you must include the DISTINCT predicate in
your listbox RowSource so that you get each city listed only once.

The trick is to just take it one step at a time if you are unsure.

First create the listbox - it should list each city only once, and you
should be able to select multiple cities. Set MultiSelect to "Simple" and
make sure ControlSource is blank.

Next you can create a command button and attach the code below to its Click
event. Note that you can use Debug.Print and MsgBox to show you how things
are progressing. For example, after each
sList = ...
in your code, you might put
Debug.Print sList
so you can see that the string is being built correctly.

Then you can try including that comma-separated string as part of a WHERE
clause and using it to filter your report.

When all that is working, you can add further listboxes to filter other
fields.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you very much for replying. I put a list box using the toolbox and
selected the fields. It then just lists all of the entries in the table
for
those fields. Am I starting off right? Will I be selecting the query off
the list box itself or through applying the code. Don't know too much
about
programming, butI think I can manage if I get a bigger picture?

Hi Confused

You need to put a multi-select listbox on your form containing all the
possible values that can be selected. Then, when the time comes to apply
your filter, you create a filter string from the selected values in your
listbox.

For example, you have a multiselect listbox named lstSelectCities with
this
RowSource:
SELECT distinct City from Customers;
This will list all the possible cities - only one instance of each.

Now, you need to make a comma-separated list of the selected cities. You
can do that like this:

Dim vItem As Variant, sList As String
Const DQ = """" ' one double-quote
With lstSelectCities
For Each vItem In .ItemsSelected
sList = sList & DQ & .ItemData(vItem) & DQ & ","
Next
sList= Left(sList, Len(sList) - 1) ' remove last comma
End If
End With

This will give you, for example: "St Louis","Miami","Omaha"

You can use the string you have constructed here to form part of your
WHERE
condition or filter string:

City In ("St Louis","Miami","Omaha")

Do a similar thing with your products. Here, you will probably want to
use
a subquery to select the customers who have ordered a product:

CustomerID In (Select Order.Customer from Orders inner join
OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where OrderItems.ProductID
In
(5,42,99))

The part that comes from your listbox is the three ProductIDs that have
been
selected:
5,42,99

Then put all of these WHERE fragments together:

SELECT * from Customers
WHERE (City In ("St Louis","Miami","Omaha"))
AND (CustomerID In (Select Order.Customer from Orders inner join
OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where OrderItems.ProductID
In
(5,42,99)))

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand



I want to run a query on a form that is bound to a Customers Table.
Combo
box
doesn't seem to do it. How do I select multiple criteria for the same
field. E.g., Show me the customers that are in St Louis, Miami, and
Omaha-Along with the customer that order product X and Z. etc. Is
this
possible?

I then want to email the customers as a result of the query through the
Contacts Table.

 

[Graham Mandeno]

Is your City field really named "Field3"? If so, the first thing I suggest
you do is open your Customers table in design view and rename that field to
something more meaningful, like "CustCity".

Now to the listbox: The wizards are intended only to perform a limited
number of fairly simple functions. To do anything more you need to get
under the hood a little.

With your form in design view, add a listbox (cancel the wizard if it pops
up) and then click on the listbox and press F4. The properties window
should appear entitled "List Box: ListXX". Make the following changes in
your properties window:
Name: lstSelectCities
Control Source: <ensure this is blank>
Row Source Type: Table/Query
Row Source:
Select DISTINCT CustCity from Customers WHERE CustCity is not Null;
Column Count: 1
Multi Select: Simple

You should now have a multi-select listbox that lists one instance of each
city in your Customers table.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK... When I create the list box I'm using the wizard. I don't see an
option
for a multiselect list box. There are three options. I select the third
one
to look up values in a table or query. It then puts this statement on the
Row Source SELECT [customers].[ID], [customers].[Field3] FROM
[customers];

Field 3 is where the list of cities are. If I change that to Select
Distinct it errors out or doesn't list anything. I could go in and
manually
type each city if I select option2. But what I'm still "confused" about
is
once I get the list box to list all of the cities, how do you select
multiple
ones? Because I can still only make one selection. So maybe the
questions
should have been first, How do I build a "multiselect list box? " Thanks
for your patience.

Hi Confused (still?

Just try it with one listbox first, to select multiple cities. Do you
have
a separate table for cities, or is it just a text field in your Customers
table? If it's the latter, then you must include the DISTINCT predicate
in
your listbox RowSource so that you get each city listed only once.

The trick is to just take it one step at a time if you are unsure.

First create the listbox - it should list each city only once, and you
should be able to select multiple cities. Set MultiSelect to "Simple" and
make sure ControlSource is blank.

Next you can create a command button and attach the code below to its
Click
event. Note that you can use Debug.Print and MsgBox to show you how
things
are progressing. For example, after each
sList = ...
in your code, you might put
Debug.Print sList
so you can see that the string is being built correctly.

Then you can try including that comma-separated string as part of a WHERE
clause and using it to filter your report.

When all that is working, you can add further listboxes to filter other
fields.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you very much for replying. I put a list box using the toolbox
and
selected the fields. It then just lists all of the entries in the
table
for
those fields. Am I starting off right? Will I be selecting the query
off
the list box itself or through applying the code. Don't know too much
about
programming, butI think I can manage if I get a bigger picture?

:

Hi Confused

You need to put a multi-select listbox on your form containing all the
possible values that can be selected. Then, when the time comes to
apply
your filter, you create a filter string from the selected values in
your
listbox.

For example, you have a multiselect listbox named lstSelectCities with
this
RowSource:
SELECT distinct City from Customers;
This will list all the possible cities - only one instance of each.

Now, you need to make a comma-separated list of the selected cities.
You
can do that like this:

Dim vItem As Variant, sList As String
Const DQ = """" ' one double-quote
With lstSelectCities
For Each vItem In .ItemsSelected
sList = sList & DQ & .ItemData(vItem) & DQ & ","
Next
sList= Left(sList, Len(sList) - 1) ' remove last comma
End If
End With

This will give you, for example: "St Louis","Miami","Omaha"

You can use the string you have constructed here to form part of your
WHERE
condition or filter string:

City In ("St Louis","Miami","Omaha")

Do a similar thing with your products. Here, you will probably want
to
use
a subquery to select the customers who have ordered a product:

CustomerID In (Select Order.Customer from Orders inner join
OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where
OrderItems.ProductID
In
(5,42,99))

The part that comes from your listbox is the three ProductIDs that
have
been
selected:
5,42,99

Then put all of these WHERE fragments together:

SELECT * from Customers
WHERE (City In ("St Louis","Miami","Omaha"))
AND (CustomerID In (Select Order.Customer from Orders inner join
OrderItems
on Order.OrderNumber = OrderItems.OrderNumber where
OrderItems.ProductID
In
(5,42,99)))

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand



I want to run a query on a form that is bound to a Customers Table.
Combo
box
doesn't seem to do it. How do I select multiple criteria for the
same
field. E.g., Show me the customers that are in St Louis, Miami,
and
Omaha-Along with the customer that order product X and Z. etc.
Is
this
possible?

I then want to email the customers as a result of the query through
the
Contacts Table.