Filter limitation on calling the Report

[Guest]

I had about more than 15 field that I needed to include on the where clause
when calling the report from a form. Using the command:

DoCmd.OpenReport stDocName, acPreview, , vDefWhere

* Where vDefWhere is String that have the filter definitions

Now I am having problems that I get an error:

The filter operation was cancelled. The filter would be too long.

Is there other way around to fix the issue without having to reduce the
fields involved?

 

[Allen Browne]

The "cancelled" operation indicates some problem with the filter string,
e.g. it is mal-formed.

You are permited 32768 characters in the WhereCondition of the OpenReport
method in code, so it is more likely that it is mal-formed than too long.

Suggestions:
- Make sure there are square brackets around any field/table name that
contains spaces or other special characters.

- Include brackets (parentheses) around the phrases of the clause,
especially where you have combinations of AND and OR, or where Between ...
And ... could be misinterpreted as AND.

- Use the IN operator for a set of alternative values instead of a series of
ORs.

- Include the table name if the field name is duplicated in the report's
source query (e.g. where a primary key in one table and the foreign key in
another table have the same name.)

- Include:
Debug.Print vDefWhere
Then after it fails, copy this phrase from the Immediate Window (Ctrl+G),
and paste it into the WHERE clause of your query, to see where it is
mal-formed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Thank you for this info you had posted. If I try the other way around by
using a query filter would that be the same?

 

[Allen Browne]

Give it a try.

If you get it working, you can then copy the WHERE clause from the query,
because that is precisely what a Filter string is.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message