counting records if certain criteria are met

[leeroi]

Hello,

I would like to create a calculation in a report that counts the number of records that meet two criteria: "ReferralDate" is not null, and if "DischargeDate" is null. The name of the record field is "ID".

How would I do this? I know that it involves using Count, Iif and IsNull, but I'm stuck.

Thanks

 

[dhookom]

Add a text box to a group or report Header or Footer section with a control source of:
=Sum(Abs(Not IsNull([ReferralDate]) AND IsNull([DischargeDate])))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[leeroi]

Wouldn't it be "=Count", rather than "=Sum"? And what does "Abs" mean?

Thanks

 

[dhookom]

Abs(...expression...) returns the absolute value of the expression. The expression I suggested should return either True/-1 or False/0. If you Sum() the absolute value of the expression it is the same as counting the true values.

If you try to Count(), it will count both true values and false values.

Did you try the expression?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]