Multi Selection List Box

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

I have a list box that I need to pick anywhere from one to twelve items out
of at any given time (fical periods actually). With the data selected from
this box I need to run a query that filters on the items chosen. I already
have the code that populates a text field called "ListStorage," which looks
something like this after a command button is pressed to run the script:
Like 1 Or Like 2 Or Like 3...

The query will not accept the data from my text box and no data is displayed
when the query is ran.

I searched the Internet and found something that runs a script that filters
the data one click at a time but I need to concatinate those clicks so I can
print a report based on the items selected.

Thank you,
Tony
 
You can't use a value like " Like 1 or Like 2 or ..." in a text box and
expect it to work in a query. It just doesn't work. You can
- modify the SQL property of a saved query
- dynamically build a where clause based on your selected items
use the where condition in the DoCmd.OpenReport method
- Use the generic multi-select list box function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
- roll your own function
 
As Duane points out, that syntax will not work. Change it to create a
string like this instead:

IN(1, 2, 3)
 
Dave/Klatuu,
If I understand the original question correctly, Tony has a text box on a
form that he is referencing in the criteria of a query like:
SELECT.....
FROM .....
WHERE [SomeField]=Forms!frmA!txtFilter
A value in the text box of "IN (1, 2, 3)" would not work in this instance.

In addition, a text box value of "1,2,3" would not work in a query with SQL
like:
SELECT.....
FROM .....
WHERE [SomeField] IN (Forms!frmA!txtFilter)

I might have misunderstood the OP.
 
Well, maybe, Duane, but the post starts off by mentioning a List Box from
which he can select up to 12 items and he already has code to do the Like 1
or Like 2, etc.
Based on that, I suggested the IN() predicate.
--
Dave Hargis, Microsoft Access MVP


Duane Hookom said:
Dave/Klatuu,
If I understand the original question correctly, Tony has a text box on a
form that he is referencing in the criteria of a query like:
SELECT.....
FROM .....
WHERE [SomeField]=Forms!frmA!txtFilter
A value in the text box of "IN (1, 2, 3)" would not work in this instance.

In addition, a text box value of "1,2,3" would not work in a query with SQL
like:
SELECT.....
FROM .....
WHERE [SomeField] IN (Forms!frmA!txtFilter)

I might have misunderstood the OP.


--
Duane Hookom
Microsoft Access MVP


Klatuu said:
As Duane points out, that syntax will not work. Change it to create a
string like this instead:

IN(1, 2, 3)
Click to expand...
Click to expand...
 
But it looks like he is sticking the results in a text box "code that
populates a text field called 'ListStorage,'". You can't use a reference to
'ListStorage' in a query like:
SELECT ...
FROM ...
WHERE [FieldName] In ( Forms!frmTonys!listStorage )

Simply placing the derived string into a text box has no purpose on its own.
I guess it would help if Tony provided the SQL view of his query. This would
be conclusive regarding how he is attempting to filter his query based on the
selected items of the list box.
--
Duane Hookom
Microsoft Access MVP


Klatuu said:
Well, maybe, Duane, but the post starts off by mentioning a List Box from
which he can select up to 12 items and he already has code to do the Like 1
or Like 2, etc.
Based on that, I suggested the IN() predicate.
--
Dave Hargis, Microsoft Access MVP


Duane Hookom said:
Dave/Klatuu,
If I understand the original question correctly, Tony has a text box on a
form that he is referencing in the criteria of a query like:
SELECT.....
FROM .....
WHERE [SomeField]=Forms!frmA!txtFilter
A value in the text box of "IN (1, 2, 3)" would not work in this instance.

In addition, a text box value of "1,2,3" would not work in a query with SQL
like:
SELECT.....
FROM .....
WHERE [SomeField] IN (Forms!frmA!txtFilter)

I might have misunderstood the OP.


--
Duane Hookom
Microsoft Access MVP


Klatuu said:
As Duane points out, that syntax will not work. Change it to create a
string like this instead:

IN(1, 2, 3)
--
Dave Hargis, Microsoft Access MVP


:

I have a list box that I need to pick anywhere from one to twelve items out
of at any given time (fical periods actually). With the data selected from
this box I need to run a query that filters on the items chosen. I already
have the code that populates a text field called "ListStorage," which looks
something like this after a command button is pressed to run the script:
Like 1 Or Like 2 Or Like 3...

The query will not accept the data from my text box and no data is displayed
when the query is ran.

I searched the Internet and found something that runs a script that filters
the data one click at a time but I need to concatinate those clicks so I can
print a report based on the items selected.

Thank you,
Tony
Click to expand...
Click to expand...
Click to expand...
 
This is true; however, it was not part of the question, so I assumed he had
that handled and the problem was a syntax issue.
--
Dave Hargis, Microsoft Access MVP


Duane Hookom said:
But it looks like he is sticking the results in a text box "code that
populates a text field called 'ListStorage,'". You can't use a reference to
'ListStorage' in a query like:
SELECT ...
FROM ...
WHERE [FieldName] In ( Forms!frmTonys!listStorage )

Simply placing the derived string into a text box has no purpose on its own.
I guess it would help if Tony provided the SQL view of his query. This would
be conclusive regarding how he is attempting to filter his query based on the
selected items of the list box.
--
Duane Hookom
Microsoft Access MVP


Klatuu said:
Well, maybe, Duane, but the post starts off by mentioning a List Box from
which he can select up to 12 items and he already has code to do the Like 1
or Like 2, etc.
Based on that, I suggested the IN() predicate.
--
Dave Hargis, Microsoft Access MVP


Duane Hookom said:
Dave/Klatuu,
If I understand the original question correctly, Tony has a text box on a
form that he is referencing in the criteria of a query like:
SELECT.....
FROM .....
WHERE [SomeField]=Forms!frmA!txtFilter
A value in the text box of "IN (1, 2, 3)" would not work in this instance.

In addition, a text box value of "1,2,3" would not work in a query with SQL
like:
SELECT.....
FROM .....
WHERE [SomeField] IN (Forms!frmA!txtFilter)

I might have misunderstood the OP.


--
Duane Hookom
Microsoft Access MVP


:

As Duane points out, that syntax will not work. Change it to create a
string like this instead:

IN(1, 2, 3)
--
Dave Hargis, Microsoft Access MVP


:

I have a list box that I need to pick anywhere from one to twelve items out
of at any given time (fical periods actually). With the data selected from
this box I need to run a query that filters on the items chosen. I already
have the code that populates a text field called "ListStorage," which looks
something like this after a command button is pressed to run the script:
Like 1 Or Like 2 Or Like 3...

The query will not accept the data from my text box and no data is displayed
when the query is ran.

I searched the Internet and found something that runs a script that filters
the data one click at a time but I need to concatinate those clicks so I can
print a report based on the items selected.

Thank you,
Tony
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane but I tried this and it only works if you select one item at a
time.

Thank you,
Tony
 
Thanks Klatuu,

I believe this is heading in the right direction but not quite. I tried
passing this but it didn't work.

I've been trying to pass this statement [FiscalPeriod] IN (2,3) for example
through the DoCmd and it runs the report but doesn't apply the filter. Any
ideas on this one?
 
I figured it out. I'm an idiot. I was looking at the wrong query when
running the script. Thanks for all your help. IN (2,3) was the right
answer.

-Tony
 
Back
Top