Filtering Reports

[jackadamson]

I am trying to produce a report but struggling.

My database is essentially as follows.

Note: Tables are linked to Excel spreadsheets and can not be editted.

Table 1 = Code, Assets Amount, Assets Date

e.g.

ABC 100 01/02/2003
ABC 800 01/03/2003
ADB 900 01/02/2003
ADB 750 01/03/2003

Table 2 = Code, Sales, Sales Date

e.g.

ABC 2 01/01/2003 to 01/02/2003
ABC 3 01/02/2003 to 01/03/2003
ADB 4 01/01/2003 to 01/02/2003
ADB 8 01/02/2003 to 01/03/2003

Query = Code, Assets, Assets Date, Sales, Sales Date

e.g.

ABC 100 01/02/2003 2 01/01/2003 to 01/02/2003
ABC 800 01/03/2003 2 01/01/2003 to 01/02/2003
ABC 100 01/02/2003 3 01/02/2003 to 01/03/2003
ABC 800 01/03/2003 3 01/02/2003 to 01/03/2003
ADB 900 01/02/2003 4 01/01/2003 to 01/02/2003
ADB 750 01/03/2003 4 01/01/2003 to 01/02/2003
ADB 900 01/02/2003 8 01/02/2003 to 01/03/2003
ADB 750 01/03/2003 8 01/02/2003 to 01/03/2003

I now want to make a report that filters the Asset Date and Sales Date so the following data is generated.

ABC 100 01/02/2003 2 01/01/2003 to 01/02/2003
ABC 800 01/03/2003 3 01/02/2003 to 01/03/2003
ADB 900 01/02/2003 4 01/01/2003 to 01/02/2003
ADB 750 01/03/2003 8 01/02/2003 to 01/03/2003

Basically as you can see, I want the Asset Date to match the end of the Sales Date.

Is this possible?

Can I do this when generating a report? Or do I need to do this earlier, at query or table stage?

Thanks!

 

[sxschech]

How are you running your report manually, through code?

You can add a filter directly to the report manually by opening the report in design view, clicking on the little square in the upper left corner of the report (on the ruler line to the left and above the "Page Header" grouping line.) Then open the properties window and go to the Data Tab. There you will see the filter. Type in your filter and on the next line change Filter On Load from No To Yes.

Alternatively, you can add the where clause to your query and the report will be based on the "filtered" results of the where clause.

It may be easier to change the filter using the query where clause as then you could change the conditions without having to first go into the design view of the report.

If using code, there is another option, but hopefully one of the above meets your needs.