NZ Assistance with DSum

[Dalan]

I seemed to be having problems with structuring the use of NZ with a
DSum expression. Having tried numerous variations of the expression
without success, I'm asking for assistance.

First some background (Access 97) - the DSum expressions are being
used in grand total text boxes on the footer of a subform. And when
viewing the subform in the linked main form the two grand total boxes
display #Error if no entry had been made. Those with entries are
calculated correctly and properly shown.

As a test, I have used =NZ([TotalSales], "0") in one of the grand
total text boxes, and yes, it does provide a "0". Incoporating NZ into
the expression allows the DSum part to work properly; however, there
is no resulting "0" on blank sales entry subforms. One of the
expressions used is: =NZ(CCur(DSum("[TotalSales]", "tblSales","IDSales
= " & [IDSales])),"0"). Yes, I have tried the expression without using
" around the 0 and without CCur, and countless other ways it seems,
but to no avail.

The TotalSales refers to the field name of a related text box which
holds the value of =[Units]*[SalesPrice]. The field name of the grand
total text box on the subform footer is GTTotalSales.

Any assistance will be appreciated. Thanks.

[Allen Browne]

Several aspects to this, Dalan.

1. Drop the quotes. They are only relevent to Text data, and I think you are
working with numbers, so you need:
=Nz([TotalSales, 0)

2. The DSum() error. Your 3rd argument is
"IDSales = " & [IDSales]
That's fine if IDSales has a value. If it doesn't the 3rd argument becomes
just:
ID Sales =
which naturally doesn't work. The solution is to provide some value for the
Null case so the argument can be evaluated. Assuming ID Sales is an
autonumber starting from 1 (so there is no zero), you could use:
"IDSales = " & Nz([IDSales], 0)

3. Can new entries be made in the subform? If not, and there are no matching
entries to display, the subform goes completely blank. If you are seeing
that kind of thing, Nz() won't solve the problem. You will need to use an
IIf() expression such as:
=IIf(IsError([TotalSales]), 0, Nz([TotalSales], 0))

4. If there are no matches, DSum() returns Null. CCur() can't handle null,
so you need to do the Nz() bit before the CCur():
=CCur(Nz(DSum("[TotalSales]", "tblSales",
"IDSales = " & Nz([IDSales], 0)), 0))
Actually, if you set the Format property of this text box to Currency, you
can probably drop the CCur() bit.

An alternative:
Create a query into the subform's table.
Type this expression into a fresh column, Field row:
Amount: [Units] * [SalesPrice]
Use this query as the the RecordSource of your query.
You can now display the Amount on each row (if desired), and include a text
box in the subform bound to:
=Sum([Amount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

I seemed to be having problems with structuring the use of NZ with a
DSum expression. Having tried numerous variations of the expression
without success, I'm asking for assistance.

First some background (Access 97) - the DSum expressions are being
used in grand total text boxes on the footer of a subform. And when
viewing the subform in the linked main form the two grand total boxes
display #Error if no entry had been made. Those with entries are
calculated correctly and properly shown.

As a test, I have used =NZ([TotalSales], "0") in one of the grand
total text boxes, and yes, it does provide a "0". Incoporating NZ into
the expression allows the DSum part to work properly; however, there
is no resulting "0" on blank sales entry subforms. One of the
expressions used is: =NZ(CCur(DSum("[TotalSales]", "tblSales","IDSales
= " & [IDSales])),"0"). Yes, I have tried the expression without using
" around the 0 and without CCur, and countless other ways it seems,
but to no avail.

The TotalSales refers to the field name of a related text box which
holds the value of =[Units]*[SalesPrice]. The field name of the grand
total text box on the subform footer is GTTotalSales.

Any assistance will be appreciated. Thanks.

[Dalan]

"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<Od********************@news-server.bigpond.net.au>...

Several aspects to this, Dalan.

1. Drop the quotes. They are only relevent to Text data, and I think you are
working with numbers, so you need:
=Nz([TotalSales, 0)

2. The DSum() error. Your 3rd argument is
"IDSales = " & [IDSales]
That's fine if IDSales has a value. If it doesn't the 3rd argument becomes
just:
ID Sales =
which naturally doesn't work. The solution is to provide some value for the
Null case so the argument can be evaluated. Assuming ID Sales is an
autonumber starting from 1 (so there is no zero), you could use:
"IDSales = " & Nz([IDSales], 0)

3. Can new entries be made in the subform? If not, and there are no matching
entries to display, the subform goes completely blank. If you are seeing
that kind of thing, Nz() won't solve the problem. You will need to use an
IIf() expression such as:
=IIf(IsError([TotalSales]), 0, Nz([TotalSales], 0))

4. If there are no matches, DSum() returns Null. CCur() can't handle null,
so you need to do the Nz() bit before the CCur():
=CCur(Nz(DSum("[TotalSales]", "tblSales",
"IDSales = " & Nz([IDSales], 0)), 0))
Actually, if you set the Format property of this text box to Currency, you
can probably drop the CCur() bit.

An alternative:
Create a query into the subform's table.
Type this expression into a fresh column, Field row:
Amount: [Units] * [SalesPrice]
Use this query as the the RecordSource of your query.
You can now display the Amount on each row (if desired), and include a text
box in the subform bound to:
=Sum([Amount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

I seemed to be having problems with structuring the use of NZ with a
DSum expression. Having tried numerous variations of the expression
without success, I'm asking for assistance.

First some background (Access 97) - the DSum expressions are being
used in grand total text boxes on the footer of a subform. And when
viewing the subform in the linked main form the two grand total boxes
display #Error if no entry had been made. Those with entries are
calculated correctly and properly shown.

As a test, I have used =NZ([TotalSales], "0") in one of the grand
total text boxes, and yes, it does provide a "0". Incoporating NZ into
the expression allows the DSum part to work properly; however, there
is no resulting "0" on blank sales entry subforms. One of the
expressions used is: =NZ(CCur(DSum("[TotalSales]", "tblSales","IDSales
= " & [IDSales])),"0"). Yes, I have tried the expression without using
" around the 0 and without CCur, and countless other ways it seems,
but to no avail.

The TotalSales refers to the field name of a related text box which
holds the value of =[Units]*[SalesPrice]. The field name of the grand
total text box on the subform footer is GTTotalSales.

Any assistance will be appreciated. Thanks.

Thanks again Allen for your response and guidance. I will try your
suggestions today and hopefully will resolve the problem. I wanted to
let you know that I was originally using a query to supply the DSum
expression totals on the subform, but . . . when adding a new sales
record to the subform while in the main form an error message would
occur: Syntax Error (missing operator) in query expression '[IDSales]
='. The only way out was to use Ctrl-Alt-Del. However, later I
discovered that after clicking OK on the error messsage about a dozen
times, the message would disappear and was able to proceed to enter
sales data on the subform (after about 7 clicks or so #Error appeared
in all fields except the date ones - strange?).

I'm still using the query for supplying the information to run the
reports, but there is a difference in the DSum expression used:
=DSum("[TotalSales]","tblSales", "IDSales = " & [IDMain])

Best regards, Dalan