"Allen Browne" <abrowne1_SpamTrap@bigpond.net.au> wrote in message news:<Odphb.144003$bo1.51019@news-server.bigpond.net.au>...[color=blue]
> 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" <other@safe-mail.net> wrote in message
> news:504f21f6.0310091512.75f71d5b@posting.google.c om...[color=green]
> > 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.[/color][/color]
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
