DCount on Report A2007

[Tom Ventouris]

I have a TexxBox on a report which returns an event date or, if no date,
returns "Pending".
I have called the TextBox "txtEvent".

=IIf([EventDate] Is Null,"Pending",[EventDate])
No Problem here.

Now I want to count the "Pending"

=DCount("[txtEvent]","MyReportName","[txtEvent] ='Pending'")

This returns "Error"

Any suggestions? Thanks in advance.

 

[fredg]

I have a TexxBox on a report which returns an event date or, if no date,
returns "Pending".
I have called the TextBox "txtEvent".

=IIf([EventDate] Is Null,"Pending",[EventDate])
No Problem here.

Now I want to count the "Pending"

=DCount("[txtEvent]","MyReportName","[txtEvent] ='Pending'")

This returns "Error"

Any suggestions? Thanks in advance.

Better look up DCount in VBA help.
DCount will count records in a table or query,. not in a report.
The table does not contain the word 'pending' in that field. In the
table that field is Null.

To count the number of records (in the table/query) in which that
field is null, you can use:
=DCount("*","TableName","[EventDate] is null")

The thing to remember however is this counts the records in the
underlying table, which may, or may not be, the same number of records
shown in the report, i.e. the report may have been filtered.

To count the number of records actually in the report in which that
field is null, you can use an unbound text control:

=Sum(IIf(IsNull([EventDate]),1,0))

Place it anywhere in the report EXCEPT in the Page Header or Page
Footer. Make sure the name of this control is NOT 'EventDate'.

 

[Jac Tremblay]

Hi Fredg,
Your reply to Tom is very interesting and full of information.
Why is it not possible to have such a field (SumIf...) in the header or
footer of a report?
I had the same problem in a previous project and since I could not do it, I
choose to update a Param table field before showing the report. It works fine
but I think there must be an easier way to show a "number of records
involved" in a header of footer.
Thanks.
--
Jac Tremblay

I have a TexxBox on a report which returns an event date or, if no date,
returns "Pending".
I have called the TextBox "txtEvent".

=IIf([EventDate] Is Null,"Pending",[EventDate])
No Problem here.

Now I want to count the "Pending"

=DCount("[txtEvent]","MyReportName","[txtEvent] ='Pending'")

This returns "Error"

Any suggestions? Thanks in advance.

Better look up DCount in VBA help.
DCount will count records in a table or query,. not in a report.
The table does not contain the word 'pending' in that field. In the
table that field is Null.

To count the number of records (in the table/query) in which that
field is null, you can use:
=DCount("*","TableName","[EventDate] is null")

The thing to remember however is this counts the records in the
underlying table, which may, or may not be, the same number of records
shown in the report, i.e. the report may have been filtered.

To count the number of records actually in the report in which that
field is null, you can use an unbound text control:

=Sum(IIf(IsNull([EventDate]),1,0))

Place it anywhere in the report EXCEPT in the Page Header or Page
Footer. Make sure the name of this control is NOT 'EventDate'.

 

[fredg]

Hi Fredg,
Your reply to Tom is very interesting and full of information.
Why is it not possible to have such a field (SumIf...) in the header or
footer of a report?
I had the same problem in a previous project and since I could not do it, I
choose to update a Param table field before showing the report. It works fine
but I think there must be an easier way to show a "number of records
involved" in a header of footer.
Thanks.

I specified Page Header and Page Footer, which work differently than
other report footers.
See Access Knowledge Base article:

132017 'How to sum a column of numbers in a report by page"

for a method to display a calculated value in the Page Header or
Footer.