dispalying results of empty set

[butthead]

I have a sub report that must be displayed
regardles of the value of the results of the
expression below.

=Count([Qry Distint count of repairs]![Repair])

the problem I am having is that if the value of
the query is an empty set I get an error when I want
a value of "0" instead. the report displays the
message "#Error" in the text object.

any help is appreciated.
access 97

 

[jrbarnett]

Change it to:

=Nz(Count([Qry Distint count of repairs]![Repair]),0)

John

 

[butthead]

the changes were made in the data/control source
property of the text control.

I changed the property value to what you suggested
and I get the same results.

 

[jrbarnett]

OK

There are two possibilities here:
1) If the "qry distinct count of repairs" query is the controlsource of the subreport, you can change it to =Count(*) to retrieve the number of records in the subform that match the criteria (eg records in "qry distinct count of repairs" related to the main record). There is no need to specify further criteria, because the Count function returns the number of records.

2) If the query is not the controlsource of the report, then you need to call the DCount VBA function with appropriate arguments:

=DCount ("*", "qry distinct count of repairs")

You can add a further option to the command to restrict the number of records counted, because otherwise it will return the total number of records in that particular query, rather than any that match specific criteria, such as:

=DCount ("*", "qry distinct count of repairs", "VehicleReg='ABCD1234'")

John