Control Source on a report 1

[vamoose]

Access 2000. I have a report which contains a text box named Text81. In the Control Source property of this text box I have placed the following: =Count([Material]) This works fine and counts the number of line item entries on the form. And now I would like to change it to read only the number of line item entries where the Actual Quantity is > 0. I have tried these: =Count([Material]) where [Actual Quantity] > 0 and Select Count([Material]) as Text81 from ABS_Daily where ((([ABS_Daily].[Actual Quantity])>0)); neither of which work. Any suggestions please and thank you.

 

[dhookom]

Try:
=Sum(Abs(Nz([Actual Quantity],0)>0))

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]

 

[vamoose]

That works GREAT !!!! Could you please explain why using sum vs. count, and what is Nz. Just trying to learn what is going on, Thank you and have a star.

 

[dhookom]

Count just counts all non-null values.

Nz() is a function that allows you to specify an alternative value if an expression is Null. In my usage, if [Actual Quantity] is null, then the expression will return 0.

Abs( [any true/false expression] ) will return either 0 if false or 1 if true. If you Sum() the expression, it sums the 1s and 0s to give you a count of the number of true expressions.

You could probably also use something like:
=Count(IIf(Nz([Actual Quantity],0)>0),1,Null)



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]