Unanswered: Is there any way to filter data in the report rather than the query?
I am running a query that shows all documents with type "TQ".
Is there any way to have the report show only those documents with revision "like "p%""? normaly i would stick this in the query, but i need to have a counter of total number of "TQ" documents on thesame report and that 'like' clause is going to shorten that list.
At the moment i have these fields in the report
RevInt(normaly set to "p%")
DocType (set to TQ)
total: iif(doctag is not null, 1, 0)
If you are opening it through code behind a command button, a filter is the last variable in the Docmd.OpenReport command.
If you are opening it through the regular DB window, go into design mode and view the report's properties. Under the data tab, set you filter and change FilterOn to Yes.
This will still affect your count as written since the filter is applied FIRST, then the data is tabulated, then the pases laid out.
As an alternative, to count something independant of the report's diplayed recordset, add a text box and use a DCount function as the control's recordsource. This will allow you to count whatever you want without being affected by the reports narowed set of records.
You do realise I'm trying to count records that are not in the report...
Yes I do. The "domain functions" are independant of the recordsource of the contain from which their called. In English, that means that it doesn't matter what the recordsource for the report is.
Check out DCount in the online help. The first part is the field you want to count. The second part is the datasource from which you are performing the count - which can be either a table or a query (ANY table or query in your database). The third part is the filter criteria (the WHERE clasue of a SELECT statement, without the word WHERE)
You can use fields from your reports in the WHERE clause, making the domain count responsive to the current record in the report.
For example, let's say I have a table of customers. I also have a table of customer orders. In my report of customers, which has only the customer table as the recordsource, I can have a DCount that counts the number of orders in the customer orders table, using the customer's ID field from the report in the WHERE clause of the DCount.
Not the most efficient way to do it, but it works just the same.