Account for null values on a report

[Rhys Davies]

Hi - i have several reports that i am creating for users to input their
required criteria - i.e. a report using query by form - they all work fine
unless the criteria a user enters results in an empty query. the fields on
the form are all summed or counted e.g. =Count([loanID]) to count the number
of loans made, =Sum([amountloaned]) to show the total amount of money loaned.
I am trying to account for the null values otherwise the fields on the
report just show 'error'.

I a trying to use the Nz function but am unsure if it will work on a field
already using another function

=Count(Nz([loanID]),0)

Does anyone know if this can be done?

Thanks,

Rhys.

 

[Allen Browne]

The problem is that if there are no record in the report, there are no text
boxes to sum, and so Access yields #Error.

To avoid this, test the HasData property of the report.
This kind of thing:
=IIf([Report].[HasData], Count("*"), Null)

 

[Rhys Davies]

thanks for pointing me in the right direction - i used the on no data option
in the end and entered a message saying there were no records available for
this report.

The problem is that if there are no record in the report, there are no text
boxes to sum, and so Access yields #Error.

To avoid this, test the HasData property of the report.
This kind of thing:
=IIf([Report].[HasData], Count("*"), Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi - i have several reports that i am creating for users to input their
required criteria - i.e. a report using query by form - they all work fine
unless the criteria a user enters results in an empty query. the fields
on
the form are all summed or counted e.g. =Count([loanID]) to count the
number
of loans made, =Sum([amountloaned]) to show the total amount of money
loaned.
I am trying to account for the null values otherwise the fields on the
report just show 'error'.

I a trying to use the Nz function but am unsure if it will work on a field
already using another function

=Count(Nz([loanID]),0)

Does anyone know if this can be done?

Thanks,

Rhys.