[AccessD] 3219 (Invalid Operation) Error When Setting a ReportFilter - "Tipping Point"

Charlotte,

You are brilliant!

The underlying query works nicely when there are no "filters" (Criteria).

As soon as I add a single Criteria, I receive an "Invalid Operation" when I try to run the query.

I don't think that I have ever run into this before.

I am considering two approaches.

1. Try to redo the query to make it less complicated so that criteria can be added.

2. Change the query to push the data to a "work table" and then use a second query (with filters) to pull the data from the work table.  The number of records is quite small (less than 500), so this approach may work.

I did not realize that Access generates new temporary queries behind the scenes.  I guess that this makes sense, however.

Thanks again for your help and insights.

Brad

    
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Charlotte Foust
Sent: Fri 6/21/2013 9:15 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] 3219 (Invalid Operation) Error When Setting a ReportFilter
 
What happens to the query if you embed the filter in it?  Does it still run
nicely?  Access generates new queries on a temporary basis when you run the
report, so there's a tipping point where the extra load causes the query to
trigger a "too complicated" message.

Charlotte

On Fri, Jun 21, 2013 at 1:27 PM, Brad Marks <BradM at blackforestltd.com>wrote:

> All,
>
> I have run into a strange error that I have not seen before.
>
> We have an Access 2007 Reporting/Inquiry application that has about 50
> reports.  Many of the reports have "dynamic" filters which enable our
> users to filter on a specific order, part, customer, date, sales rep,
> etc.
>
> This is accomplished via buttons on the reports (the reports are viewed
> in "Report View").  Each button opens up a unique "filter form" which
> collects the filter info from the user and then "sets" the report's
> filter and turns the filter on with VBA code that looks like this.  This
> approach has worked nicely for over 3 years.
>
> Reports.Report100.Filter = str_Where_Condition
> Reports.Report100.FilterOn = True
>
>
>
> Recently, the underlying query for one of our reports was enhanced to
> obtain more data from additional tables.  The underlying query works
> nicely when run in stand-alone mode.
>
> The problem is that we are now receiving a "3219" error when we try to
> set the report filter.
>
> This report and its filters were working fine before we added the new
> fields to the query.
>
> I am puzzled.  If the query works nicely when it is run stand-alone, I
> can't understand why setting the report filter would be a problem.  No
> other changes have been made.
>
> Perhaps I am missing something.
>
> Has anyone else ever run into this situation?
>
> Thanks,
> Brad
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
This message was scanned by ESVA and is believed to be clean.
Click here to report this message as spam. 
http://h0stname/cgi-bin/learn-msg.cgi?id=50F6C28CB0.C35C1