By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,747 Members | 1,345 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,747 IT Pros & Developers. It's quick & easy.

Open Report filtering from a listbox

P: n/a
Hi!
I'm trying to get help to an unexpected problem that has appeared
while I was writing a new application.
I want to opeon a report of product sales by filtering previously from
a listbox in a previous form. In the listbox I have the complete
products list and I select the ones I want to be presented in the
report. I push a cmdButton and generate a String (wherecondition)
which I use with docmd.openreport. The string is generated this way:

Dim contador As Long
contador = 0
For Each ItemSel In Me!Lista02.ItemsSelected
'Generate the filter for the products list concatenating field
codart
'with quotations because that field is text.
strFiltro = strFiltro & "[tblProgram].[Codart] = " &
Me![Lista02].ItemData(ItemSel) & " OR "
contador = contador + 1
Next
'Supr the OR string at the end of the where condition
' if the filter exists

If strFiltro <> "" Then
strFiltro = "(" & Left(strFiltro, Len(strFiltro) - 4) & ")"
strFiltro = strFiltro
Here is my problem when I select more than 50 products
(approsimately)I have an error box. I think the problem is caused
because the where condition is limited in its longitude and when my
string gets longer, ms access protests. The string is generated with
product's code formed by 5 alfa-numeric digits.
The concrete question is: Does anyone know any other method that
allows me to get my report without this problem of longitude of
string. I wouldn't like to present to the user that he is not able to
select more than 40-50 different products.
Naturally, the option to select "all" the products it's not a problem
but the option to select multiple products that makes the string grows
longer is very usual in my final objective.

Thanks in advance! I know for sure that some of you will light my way.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the IN keyword. E.g.:

....WHERE Codart IN ('abc12', 'xyz34', '25qrs')

If there is a limit on the size of the list (the items in the
parentheses) - I don't know it.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP+zgsYechKqOuFEgEQJY3wCgrIWw4si1KOEtiQ3Qv+Z37Y F+XnwAoOwu
hFLk1gcA8LxfG/7HHmub8AEO
=nfKK
-----END PGP SIGNATURE-----
diskoduro wrote:
Hi!
I'm trying to get help to an unexpected problem that has appeared
while I was writing a new application.
I want to opeon a report of product sales by filtering previously from
a listbox in a previous form. In the listbox I have the complete
products list and I select the ones I want to be presented in the
report. I push a cmdButton and generate a String (wherecondition)
which I use with docmd.openreport. The string is generated this way:

Dim contador As Long
contador = 0
For Each ItemSel In Me!Lista02.ItemsSelected
'Generate the filter for the products list concatenating field
codart
'with quotations because that field is text.
strFiltro = strFiltro & "[tblProgram].[Codart] = " &
Me![Lista02].ItemData(ItemSel) & " OR "
contador = contador + 1
Next
'Supr the OR string at the end of the where condition
' if the filter exists

If strFiltro <> "" Then
strFiltro = "(" & Left(strFiltro, Len(strFiltro) - 4) & ")"
strFiltro = strFiltro
Here is my problem when I select more than 50 products
(approsimately)I have an error box. I think the problem is caused
because the where condition is limited in its longitude and when my
string gets longer, ms access protests. The string is generated with
product's code formed by 5 alfa-numeric digits.
The concrete question is: Does anyone know any other method that
allows me to get my report without this problem of longitude of
string. I wouldn't like to present to the user that he is not able to
select more than 40-50 different products.
Naturally, the option to select "all" the products it's not a problem
but the option to select multiple products that makes the string grows
longer is very usual in my final objective.

Thanks in advance! I know for sure that some of you will light my way.


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.