Count problem in a report footer

[ckleung]

HI,

I want to generate the number of the result of the query in the report. But if there is no record show that means there is no admission in that month, the report footer will show error instead of "0" which I want the report show. I put the following code at the footer:

=Sum(IIf([Please enter the year：e.g.2005]=Year([AdmitDate]) And [Please enter the month: e.g. 5]=Month([AdmitDate]),1,0))

Can anyone help ? Thanks in advance.

 

[Smart]

RE; Count problem in report footer

Try placing the iff statement in the query
Total: IIf([Please enter the year：e.g.2005]=Year([AdmitDate]) And [Please enter the month: e.g. 5]=Month([AdmitDate]),1,0))

Then in the report do the sum on field returned from the query
=sum(Total)

 

[ckleung]

Does not work

Smart,

I have tried this before, but it can't work. I also try the following:

=IIf(IsNull(Count([EngName])),0,sum([Total]))

but it still don't work. Have any idea?

CKLeung

 

[Rich]

=Sum(Nz([YourField],0)

 

[ckleung]

Still does not work

=Sum(Nz([YourField],0)

Rich,

It still does not work. I do as Smart say and write the code as you say. But it still prints "error" when there is no record found. Is there anything I have done wrong.
Any way, thanks for your advice.

ckleung

 

[Rich]

There's a difference between records with Null values and No records.
If you have No records in what I assume is a subReport than you'll have to use the HasData method. You should be able to find examples of that method here.

 

[ckleung]

HasData can't work

There's a difference between records with Null values and No records.
If you have No records in what I assume is a subReport than you'll have to use the HasData method. You should be able to find examples of that method here.

Rich,

I explain my problem once. I create a report that print the clients admitted in a month, the source is from a query. At the report footer I have a textbox. I write the code in the control source:
=IIf("rptAdmissionCount.Report.HasData",Sum([This Month Admission]),0)

This Month Admission: IIf([Please enter the year: 2005]=Year([AdmitDate]) And [Please enter the month: 5]=Month([AdmitDate]),1,0) is a field I set in the query.

There is no subreport.

What's wrong with it? Can you help?