null values on a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen - 13 Nov 2006 15:41 GMT

I have a report that I use unbound controls to total groups on. There
are four groups of products on the report but it's not sorted by the
group so I can't total normally. I used control sources like this

----------
=DSum("[commission]","[EOM commissions Mullarkey]","[EOM commissions
Mullarkey]!CATEGORY='UDV '")
---------
for each of the four groups. Then I have an unbound control with the
control source of

------------
=[COMMISS-UDV]+[COMMISS-STRIPS]+[COMMISS-LABS]+[COMMISS-MISC]
------------
to total those group totals.

This month one of the groups didn't have any sales in it so there no
result for the DSum and then the totals didn't show a value either.
Does anyone know how I can get a 0 to show for the group total? I'm
guessing that the total doesn't show because of the null value for the
group.

I'm using A2003 and my database is in A2000 file format.
Thanks for your help.

Karen

Reply to this Message

Marshall Barton - 13 Nov 2006 17:57 GMT

>I have a report that I use unbound controls to total groups on. There
>are four groups of products on the report but it's not sorted by the
[quoted text clipped - 20 lines]
>I'm using A2003 and my database is in A2000 file format.
>Thanks for your help.

Seems like a strange way of doing things, but to answer your
question, use the Nz Function around the DSum.

=Nz(DSum("[commission]","[EOM commissions Mullarkey]","[EOM
commissions Mullarkey]!CATEGORY='UDV '"), 0)

Signature

Marsh
MVP [MS Access]

Reply to this Message

Karen - 13 Nov 2006 19:12 GMT

Thanks for the help. The Nz function was what I needed. I found some
information about it online but wasn't sure if it would work with this.

I know the sorting on the report doesn't make sense when you only see
the information about the totals. The report is sorted by order number
and then the products on each order are shown. There may be products
from several different categories on each order. The totals (DSum) add
up the qty sold and the sales for each of the categories.

Karen

> >I have a report that I use unbound controls to total groups on. There
> >are four groups of products on the report but it's not sorted by the
[quoted text clipped - 26 lines]
> =Nz(DSum("[commission]","[EOM commissions Mullarkey]","[EOM
> commissions Mullarkey]!CATEGORY='UDV '"), 0)

Reply to this Message