How to get around "filter too long" run-time error?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

MS Access 2K, Windows XP
====================
Hello there,

I have a form where I specify criteria to display records. The records are
displayed in a list on the same form. The user can then select records
displayed in the list and run a report on those selected records.

I used a combination of code from here:
http://www.mvps.org/access/forms/frm0007.htm
and here:
http://allenbrowne.com/ser-50.html
to construct my strWhereCondition string for opening the report.

It all works fine and the report opens and displays the records selected
from the list (I tried by selecting 4-5 records),
EXCEPT when I tried to select all the records (95 or so) displayed in the
list, I got a run-time error that said "filter too long".

I've checked the ng (forms and reports) to find an answer, without success.

The WHERE string is in the form "[queryname]![fieldname] = <selected list ID
1> OR [queryname]![fieldname] = <selected list ID 2> OR ......", so that
does add 38 or so character per ID just for the name of the field.

Is there another way to skin this cat?

Next, I'll try constructing a list of IDs and try the " WHERE [ID] IN
(list)" and see if that works.

But, if you have a solution to this problem, please let me know.

Thanks.

-Amit
 
Amit said:
MS Access 2K, Windows XP
====================
Hello there,

I have a form where I specify criteria to display records. The records are
displayed in a list on the same form. The user can then select records
displayed in the list and run a report on those selected records.

I used a combination of code from here:
http://www.mvps.org/access/forms/frm0007.htm
and here:
http://allenbrowne.com/ser-50.html
to construct my strWhereCondition string for opening the report.

It all works fine and the report opens and displays the records selected
from the list (I tried by selecting 4-5 records),
EXCEPT when I tried to select all the records (95 or so) displayed in the
list, I got a run-time error that said "filter too long".

I've checked the ng (forms and reports) to find an answer, without success.

The WHERE string is in the form "[queryname]![fieldname] = <selected list ID
1> OR [queryname]![fieldname] = <selected list ID 2> OR ......", so that
does add 38 or so character per ID just for the name of the field.
Click to expand...

Could you show your "OpenReport " line?

There is a parameter "FilterName" and a parameter "WhereCondition".
WhereCondition has a max of 32768 characters
32768 / 95 gives you over 340 characters per selected id to play with.

Regards John
Is there another way to skin this cat?

Next, I'll try constructing a list of IDs and try the " WHERE [ID] IN
(list)" and see if that works.

But, if you have a solution to this problem, please let me know.

Thanks.

-Amit
Click to expand...
 
:

Could you show your "OpenReport " line?

There is a parameter "FilterName" and a parameter "WhereCondition".
WhereCondition has a max of 32768 characters
32768 / 95 gives you over 340 characters per selected id to play with.
Click to expand...

Here's the openreport line:

DoCmd.OpenReport strDocName, acPreview, , strWhereCondition

The error number is 7769. The length(strWhereCondition) = 4557.

Maybe I can explain the set-up.
The report has a query as its record source. The strWhereCondition in the
form has [queryname]![fieldname] in it. Could that be the problem?

-Amit
 
:

Could you show your "OpenReport " line?

There is a parameter "FilterName" and a parameter "WhereCondition".
WhereCondition has a max of 32768 characters
32768 / 95 gives you over 340 characters per selected id to play with.
Click to expand...

This is what I found when I googled the error number:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;207615

But, now I'm up against another wall.
=======================================================
Method 1
Instead of using the wherecondition argument, use the filtername argument of
the OpenReport method to pass the name of a saved query. This method allows
you to filter a report without limitation on the size of the WHERE clause.
=======================================================
How do I go about doing this from my current set-up?
How do I save a query from the form with the selected list items?

Here's what I think the steps would be:

1. In the OnClick event of the button on the form that runs the report,
somehow save/specify a query with the selected list items.
2. Open the report with the name of the query.

I guess my issue is I have no idea how to do 1.

Any help would be appreciated.

Thanks.

-Amit


-Amit
 
Amit said:
:

Could you show your "OpenReport " line?

There is a parameter "FilterName" and a parameter "WhereCondition".
WhereCondition has a max of 32768 characters
32768 / 95 gives you over 340 characters per selected id to play with.
Click to expand...

Here's the openreport line:

DoCmd.OpenReport strDocName, acPreview, , strWhereCondition

The error number is 7769. The length(strWhereCondition) = 4557.

Maybe I can explain the set-up.
The report has a query as its record source. The strWhereCondition in the
form has [queryname]![fieldname] in it. Could that be the problem?

-Amit
Click to expand...

Ha Ha, so they lie

the 32768 number is from Access2000 help.

but http://support.microsoft.com/default.aspx?scid=kb;EN-US;207615
states the max is 2000

2000 / 95 = 21 chars, so using IN() and assuming the IDs are numbers should
fit fine.

Regards John

==========================
Symptoms

When you run a procedure that contains the OpenReport method of the DoCmd
object, and you use the wherecondition argument to pass a string that
contains more than 2000 characters, the report may not open. You may also
receive the following error message:
Run-time error '7769':

The filter operation was canceled. The filter would be too long.
Note that the number of characters in the wherecondition argument that
causes this behavior may vary. For example, the problem may not occur until
the length of the wherecondition argument exceeds 2048 characters.
==========================
 
Amit said:
:



This is what I found when I googled the error number:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;207615
Click to expand...

Me too
But, now I'm up against another wall.
Click to expand...

If the IDs are numbers then IN() should work.
I cannot see them getting longer than 5 characters.

=======================================================
Method 1
Instead of using the wherecondition argument, use the filtername argument of
the OpenReport method to pass the name of a saved query. This method allows
you to filter a report without limitation on the size of the WHERE clause.
Click to expand...

If the criteria is from a table this also would work, this would require a
little work
but far less than saving queries for X posibilities.

However before continueing I should say that so far I have been trying to
come to a workaround.

Because if the user is expected to choose such a large number of categories
from
what I can only assume is an even greater number of posibilities they
will undoubtably
find a way to reduce their workload.
ie make sure all companies belong to ID=101, or select all with
[Control]-[A].

After all Google doesn't require that much effort, and it searches a far
greater amount
(of unstructured) information.

So what is the problem you are trying to solve (not the database/software
problem)?

Regards John
 
Back
Top