Counting on a report based on visibility 2

[wx5chr]

I have a report with a hidden field (VIS). This field's .visible property is set to True when it is past due and False when it's not.

I'd like to count the number of Overdue items on the report and place it in the Report Footer.

 

[hneal98]

base your count on the same formula that you calculate your overdue with. Just create a calculated field with that formula in it.

 

[GingerR]

in the query that your report is based on, add a calculated field using whatever it is that you use to calculate 'overdue', and if the record is OVERDUE, set this calculated field to 1, otherwise set it to 0. then at the report footer, simply add this field: something like this:

Overdue: iif(now()>[DueDate],1,0)

where [DueDate] is your due date field. in any case, make this calculated field in your query that the report is based on. then in the report design, bring that field into the detail section with the rest of each record. you can make it invisible.

then in the report footer, put a text box and in it put

=sum(Overdue)

 

[dhookom]

You can even get the count without adding the column to the query. Add a text box to the report footer with a control source like:
=Abs(Sum([DueDate]<Date()))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[wx5chr]

Thanks for the replies. I tried adding the ABS(SUM) to my footer, however, it's counting values that are not displayed on my report. This is the same report dhookom helped me with yesterday.

I am storing the history of each version of a procedure. For example:
ILS RWY 22 AMDT 2 12/25/2003 DUE 12/24/2005
ILS RWY 22 AMDT 1 10/01/1997 DUE 9/30/1999

I need to retain both for historical purposes, however, I only want the first one to be displayed on my report, which I was helped with yesterday. However, my query pulls both when building the report. Therefore, the ABS(SUM) actually counts AMDT 1 as OVERDUE, even though AMDT 2 has superseded it and even though it's not displayed on the report.

 

[GingerR]

so do you still need help? if you want only the 'first' one to be displayed, it looks like what you want is the LATEST one, the one with the latest (most recent) date on it? you would need to adjust the recordset that the report is based on then. you need a different query. have you tried that?

 

[wx5chr]

I did end up redesigning my query and everything is corrected now. Thanks for all the help and quick responses from everyone.