Multiselect listbox to filter another listbox

[Guest]

I have a multi-select listbox with filter criteria in it (say, customer_type)
Based on the selection(s), I'd like to make an unbound listbox show only
those records that satisfy such selected criteria. Now I know one way to do
this is to construct an sql statement based on filter criteria and use it as
rowsource for the listbox, but this appears to be not quite optimal. The way
I'd rather have it is to specify the condition in the query builder at design
time via a variable (i have the function specified as the condition in the
querybuilder which returns the variable). This causes type mismatch, though.

Table: Customers
Fields: ID, Name, customer_type (int)
The condition expression is, for example, "1 or 6 or 12".
If i enter it into the query builder for listbox's rowsource at design time,
it works. But if i enter it in quotes, it obviously doesn't, and i guess as
the condition string is being constructed while parsing the multiselect
listbox and stored in a string variable, which in turn is handed via a
function to the Rowsource, it is in these "quotes" where the problem lies. Is
there a way to convert this type somehow, for i don't want to code the line
to produce ...WHERE (((Customers.ShipmentMethod)=1 Or
(Customers.ShipmentMethod)=6 Or (Customers.ShipmentMethod)=12))... type of
statement. Thank you in advance!

 

[OldPro]

I have a multi-select listbox with filter criteria in it (say, customer_type)
Based on the selection(s), I'd like to make an unbound listbox show only
those records that satisfy such selected criteria. Now I know one way to do
this is to construct an sql statement based on filter criteria and use it as
rowsource for the listbox, but this appears to be not quite optimal. The way
I'd rather have it is to specify the condition in the query builder at design
time via a variable (i have the function specified as the condition in the
querybuilder which returns the variable). This causes type mismatch, though.

Table: Customers
Fields: ID, Name, customer_type (int)
The condition expression is, for example, "1 or 6 or 12".
If i enter it into the query builder for listbox's rowsource at design time,
it works. But if i enter it in quotes, it obviously doesn't, and i guess as
the condition string is being constructed while parsing the multiselect
listbox and stored in a string variable, which in turn is handed via a
function to the Rowsource, it is in these "quotes" where the problem lies. Is
there a way to convert this type somehow, for i don't want to code the line
to produce ...WHERE (((Customers.ShipmentMethod)=1 Or
(Customers.ShipmentMethod)=6 Or (Customers.ShipmentMethod)=12))... type of
statement. Thank you in advance!

The SQL "IN" statement will work for querying a list of records.
Here is an example of how to use it:
SELECT LastName, FirstName, City FROM PhoneBook
WHERE [City] In ("Los Angeles","San Fernando","Santa Monica")

or

WHERE [id] IN (23423,45343,34443)

 

[Guest]

Thank you, this should solve my problem, and I would consider the question
resolved; still, I wonder if there is a different approach to the solution,
such as to accomodate for a string constructed in runtime returned by the
aforementioned function specified as a constraint in the query builder for a
listbox in design time. The way you propose is somewhat more elegant, but who
knows if the direct approach I seek might come in handy in other situations.
Should such approach be impossible, though, I will consider the question
closed.

Thank you, OldPro!

"OldPro" пишет:

I have a multi-select listbox with filter criteria in it (say, customer_type)
Based on the selection(s), I'd like to make an unbound listbox show only
those records that satisfy such selected criteria. Now I know one way to do
this is to construct an sql statement based on filter criteria and use it as
rowsource for the listbox, but this appears to be not quite optimal. The way
I'd rather have it is to specify the condition in the query builder at design
time via a variable (i have the function specified as the condition in the
querybuilder which returns the variable). This causes type mismatch, though.

Table: Customers
Fields: ID, Name, customer_type (int)
The condition expression is, for example, "1 or 6 or 12".
If i enter it into the query builder for listbox's rowsource at design time,
it works. But if i enter it in quotes, it obviously doesn't, and i guess as
the condition string is being constructed while parsing the multiselect
listbox and stored in a string variable, which in turn is handed via a
function to the Rowsource, it is in these "quotes" where the problem lies. Is
there a way to convert this type somehow, for i don't want to code the line
to produce ...WHERE (((Customers.ShipmentMethod)=1 Or
(Customers.ShipmentMethod)=6 Or (Customers.ShipmentMethod)=12))... type of
statement. Thank you in advance!

The SQL "IN" statement will work for querying a list of records.
Here is an example of how to use it:
SELECT LastName, FirstName, City FROM PhoneBook
WHERE [City] In ("Los Angeles","San Fernando","Santa Monica")

or

WHERE [id] IN (23423,45343,34443)