Re: Nz Function in Control Source

On May 30, 3:50 pm, "paii, Ron" <n...@xxxxxx> wrote:
"colin spalding" <colin.mard...@xxxxxxxxxxxxxxx> wrote in message

news:9240f596-c0c7-476b-8a46-d4d127d44dfe@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Access 2003

I subform which lists financial transactions for a client in the main
form, which i total in a textbox named "txtTotalPremium" in the
subform footer with the Control Source "=Sum([PremiumGBP])"; without
quotations of course.  This works fine until the subform has no
records to return in which case textbox is blank.  That would be fine
except I need to use the figure in another text box on the main form
as part of another calculation.  I have tried hundred different
combination of the Nz and IIf functions all to no avail.  it seems to
me that the straight forward "=Nz(Sum([PremiumGBP]),0)" should do it,
but unfortunately not; whatever I do I get a blank textbox and
"#Error" and/or #Name?" in the mainform textbox,  Where do I go from
here?

=Sum(NZ([PremiumGBP],0))

NZ needs to be inside the sum otherwise you may try to sum a NULL.

You get #Name when subform is empty because [PremiumGBP] field does not
exist. You will need to check for a no data condition in the subform.

Is [PremiumGBP] a numeric field? If not you will need
=Sum(iif(IsNumeric(NZ([PremiumGBP],0)),[PremiumGBP],0).

Thanks Ron

Yes the field [PremiumGBP] definitely exists and yes it is numeric
(assuming Currency counts as numeric). I have tried both solutions
you suggest but still no joy.
.

Relevant Pages

  • Re: MainForm, SubForm and SubSubForm
    ... Name of a subform control can be different from the name of the form that is ... another one Textbox to effect ... > another sum. ...
    (microsoft.public.access.forms)
  • Re: Msgbox if query no result displayed
    ... it's a sum of that subform's column? ... put a hidden textbox and use it to ... Subform is the name of the subform control (the control that holds the ... > I have Select Query based on table. ...
    (microsoft.public.access.queries)
  • Re: Help with form/subform problem?
    ... subform and put a new textbox as you suggested. ... I've got a form called frmCust that has a subform on it ... if the customer's balance is NOT zero. ...
    (comp.databases.ms-access)
  • Re: Referencing problem
    ... to return a reference to the form the tab is embeded in. ... If the tab is on a subform, ... You can't use the Me operator or the form reference returned by Me ... located on this subform and the textbox is located on the ...
    (comp.databases.ms-access)
  • RE: Setfocus to a textbox
    ... SetFocus to txtLPN ... txtCurrentASN - Textox on the Main Form ... The subform is linked to txtCurrentASN by Master/Child feilds. ... triggering after the On_Exit event of the textbox? ...
    (microsoft.public.access.forms)