Null values in Reports

[IndyGill]

I currently have an Access 2k Db, which produces a report

The problem
I have a footer which counts the number of records outputted by the report, which is:

=Count(*)

However if there is no records to count it returns a "Error" value, how do I get it to return a "0" value

Many thanks in advance

Indy

 

[Guest_imported]

Look in the help file for the syntax Nz(expression, value if null, value if not null) (I admit it's been a while, but I always use to have this problem and I found this function through diligent reading).

This gets to interrogate if the resulting value is null. If so display 0, otherwise the actual value. For example it may look something like

=Nz(Count(*), 0, Count(*))

Hope it helps.

 

[IndyGill]

Dear Aunty Access

I tried the above expression to output a Zero value, however I get the following error message:

"The expression you eneterd has a function containing the wrong number of arguments"

Please help

thanks in advance

Indy

 

[WilsonM]

Hi,

If I'm not mistaking that function only have one arg.

Try this:

=Nz(Count(*), 0)

Good luck,

 

[IndyGill]

Hi Wilson M

I tried your expression: =Nz(Count(*),0)
and I still get an "Error" message

Please help I think im going mad if I dont get my head round this soon

Thanks in advance

 

[lameid]

Create a check box control on your report and set its visible property to false.

On the form's on open event set it to true.
On the form's no data event set it to false.

Assuming the check box is named chkFlag then set your control = iif(chkFlag = true, count(*), 0)

Be sure to include the "=" sign when you set the value of the control (it is obnoxious to find if you don't know what to look for).

 

[IndyGill]

Thanks Lameid

But I was wondering is any other ways to get round this problem?

Thanks in advance

 

[Guest_imported]

Do you keep getting the same error message with the Nz expression (thanks to WilsonM for clearing up the uncertainty).

The Nz function is not available in Microsoft Access version 2.

Actually, I'm wondering if Count(*) would actually work within a report. A report is basically a huge iterative machine. Would count(*) not specify which records to act on.

You could run a quick checksum on the number of records returned before opening the report. If recordset.EOF then msgbox "No records to display" sort of thing. Then close the report.

 

[Guest_imported]

In access each report has a 'on no data' event. This will tell you there are actually no records to display.

example:
OnNoData
msgbox ("Currently no data&quot
cancel = true

Hope this helps you
Kind regards Peter Odekerken

 

[IndyGill]

The problem in more detail

1) I have a lengthly report with several sub reports
2) I want to count and do variouse sums within the subreports.
3) The figuers worked out in the subreports are then used within the main report to produce an overall figure
4) However if one of my sub reports does not contain any information the sum and count function returns a "error" message
5) Because one of the subreports has returned an "error" value rather than a "0", it messes up my overall sum within the main report.

Please help,