Using WHERE in dcmd.openreport

[SeeThru]

Bit of an odd one.....

I've got a report that I'm opening using docmd and filtering the records with a WHERE clause.

Works fine normally, but I am trying to filter on a field that is not in the query grid in report data designer.

(It's the many side of a one to many link, I just want to be able to filter for Is Null or Is not Null, but only return the Single Record side)

If I try to do this, I get a popup asking for the value in the field. If I add the field to the design grid it messes things up as each record appears multiple times.

At the moment I have a calcualted field that returns a boolean value if there are sub records or not, but Iit makes big reports really slow to run.

Thanks in advance

 

[AlexMidd]

What is the relationship between the tables and how is the boolean value calculated?

Have fun! )

Alex Middleton

 

[SeeThru]

Relationship is one to many on Primary Key.

The boolean bit works fine - it passes the Main recrods primary key to a function that opens the secondary table, and returns TRUE if there are 1 or more records, or FASLE if there are 0 records. However, it is really slow when doing a report with 2000+ records, especially on slower machines.

If I add the field manually, and enter the criteria in the query behind the report, it works fine. It just wont let me select the field as criteria useing docmd.openreport

 

[SeeThru]

I'm going to try setting the entire recordsource on the fly....