main form totals fail when subform total is null

[mbr96 via AccessMonster.com]

I have three subforms on a main form, each with a text box with a Sum() in
the subform footers. The main form has a total field of all three subform
totals. It works fine, but returns #Error if any of the subform totals are
null. I get that, and I've handled a simpler situation with totals with
nulls, (Iif function to return zero on a field if it's null so a Sum function
will work), but I'm getting an error with the subform refs here for some
reason and I'm stumped.

I simplified this situation with a text box on the main form for just one of
the subform totals instead of all three, and the Iif still fails when that
subform returns no rows. One solution would be to create a blank row for the
subform recordsource to force a zero total, but the underlying queries are
quite complex so that has me baffled too. Any suggestions as to how I can
get the main form total to return zero if the subform has no rows and a null
total? Here's the expression I'm using in the control source property on the
text box on the main form:

=IIf(([Forms]![All]![RegLocActCT3].[Form]![TotMoAct]) Is Null,0,[Forms]![All]!
[RegLocActCT3].[Form]![TotMoAct])

Works fine if RegLocActCT3 has records, so subform ref is okay, but Iif
doesn't return 0?

Any ideas? Thanks alot!

Mbr

 

[Brian]

Look at the NZ function. It is a simpler way to do what you are doing with
your IIf.

NZ(abc,0) returns abc if not null, 0 if abc is null

Use this in TotMoAct on the RegLocActCT3 form so that it returns 0 if null.
Then your other control will not need handle nulls at all, because it will
always get a zero from the subform.

Alternatively, you can try this for the main form control:

=NZ([Forms]![RegLocActCT3].[Form]![TotMoAct],0)

 

[mbr96 via AccessMonster.com]

Thanks. Trying your suggestion, familiar with Nz somewhat, but Still
struggling.

Here's the control source of the control on the subform footer that needs the
0 returned

=Sum([ActualsTot]) works fine except when null recordset

I change it to this and still get a null on subform total and #Error on main
form ref

=Nz(Sum([ActualsTot]),0)

I also tried your control source for the main form ref to one subform total

=NZ([Forms]![RegLocActCT3].[Form]![TotMoAct],0)

text shows #Name? message if done that way.

Still confused...

Look at the NZ function. It is a simpler way to do what you are doing with
your IIf.

NZ(abc,0) returns abc if not null, 0 if abc is null

Use this in TotMoAct on the RegLocActCT3 form so that it returns 0 if null.
Then your other control will not need handle nulls at all, because it will
always get a zero from the subform.

Alternatively, you can try this for the main form control:

=NZ([Forms]![RegLocActCT3].[Form]![TotMoAct],0)

I have three subforms on a main form, each with a text box with a Sum() in
the subform footers. The main form has a total field of all three subform

[quoted text clipped - 22 lines]

Mbr

 

[Brian]

Maybe I don't have all the names correct, and I think I got the "Form" in the
wrong place. Send back the names of the form, subform, form footer total
control & subform footer total control. Or substitute where necessary below:

Subform footer control value (assuming ActualsTot is the name of a control
in the subform's detail section)

=Nz(Sum([SubformDetailControlName]),0)

Maybe this? =Nz(Sum([ActualsTot]),0)

Main form footer control value:

=Forms.MainFormName.SubFormName.Form.SubformFooterControlName

maybe this? =Forms.RegLocActCT3.TotMoAct.Form.ActualsTotFooter

Thanks. Trying your suggestion, familiar with Nz somewhat, but Still
struggling.

Here's the control source of the control on the subform footer that needs the
0 returned

=Sum([ActualsTot]) works fine except when null recordset

I change it to this and still get a null on subform total and #Error on main
form ref

=Nz(Sum([ActualsTot]),0)

I also tried your control source for the main form ref to one subform total

=NZ([Forms]![RegLocActCT3].[Form]![TotMoAct],0)

text shows #Name? message if done that way.

Still confused...

Look at the NZ function. It is a simpler way to do what you are doing with
your IIf.

NZ(abc,0) returns abc if not null, 0 if abc is null

Use this in TotMoAct on the RegLocActCT3 form so that it returns 0 if null.
Then your other control will not need handle nulls at all, because it will
always get a zero from the subform.

Alternatively, you can try this for the main form control:

=NZ([Forms]![RegLocActCT3].[Form]![TotMoAct],0)

I have three subforms on a main form, each with a text box with a Sum() in
the subform footers. The main form has a total field of all three subform

[quoted text clipped - 22 lines]

Mbr

 

[mbr96 via AccessMonster.com]

As you suggested I already tried this, I set the control source for the total
control in the subform footer, called TotMoAct:

=Nz(Sum([ActualsTot]),0)

it still does not return a 0 if underlying recordset is null, meaning there
are no values for ActualsTot.

We already have the reference to the control for the subform footer totals
correct, as I stated, but they still go blank if the subform's recordset is
null. Your suggestion has the ToMoAct and Form part switched, =Forms.
RegLocActCT3.TotMoAct.Form.ActualsTotFooter

and since the name of the subform footer control with the total is TotMoAct,
the expression works like this
=[Forms]![All]![RegLocActCT3].[Form]![TotMoAct]

but it still goes blank if there are no ActualsTot rows, hence no TotMoAct to
refer to. Some other expression or method, anyone?

Appreciate the suggestions, Brian, too.

Mbr


Trying to figure out a way to force a zero row here.

Maybe I don't have all the names correct, and I think I got the "Form" in the
wrong place. Send back the names of the form, subform, form footer total
control & subform footer total control. Or substitute where necessary below:

Subform footer control value (assuming ActualsTot is the name of a control
in the subform's detail section)

=Nz(Sum([SubformDetailControlName]),0)

Maybe this? =Nz(Sum([ActualsTot]),0)

Main form footer control value:

=Forms.MainFormName.SubFormName.Form.SubformFooterControlName

maybe this? =Forms.RegLocActCT3.TotMoAct.Form.ActualsTotFooter

Thanks. Trying your suggestion, familiar with Nz somewhat, but Still
struggling.

[quoted text clipped - 35 lines]

 

[Brian]

Sorry. I have been unable to duplicate this. In my testing, I get 0 on the
calculated subform footer even when there are no records on the subform. I
must be making an invalid assumption about some other element(s) of the setup
of your form/subform. In my testing, both the form & subform were bound, and
the reference in the subform footer total control was to a field in the
subform's record source. Access 2003.

As you suggested I already tried this, I set the control source for the total
control in the subform footer, called TotMoAct:

=Nz(Sum([ActualsTot]),0)

it still does not return a 0 if underlying recordset is null, meaning there
are no values for ActualsTot.

We already have the reference to the control for the subform footer totals
correct, as I stated, but they still go blank if the subform's recordset is
null. Your suggestion has the ToMoAct and Form part switched, =Forms.
RegLocActCT3.TotMoAct.Form.ActualsTotFooter

and since the name of the subform footer control with the total is TotMoAct,
the expression works like this
=[Forms]![All]![RegLocActCT3].[Form]![TotMoAct]

but it still goes blank if there are no ActualsTot rows, hence no TotMoAct to
refer to. Some other expression or method, anyone?

Appreciate the suggestions, Brian, too.

Mbr


Trying to figure out a way to force a zero row here.

Maybe I don't have all the names correct, and I think I got the "Form" in the
wrong place. Send back the names of the form, subform, form footer total
control & subform footer total control. Or substitute where necessary below:

Subform footer control value (assuming ActualsTot is the name of a control
in the subform's detail section)

=Nz(Sum([SubformDetailControlName]),0)

Maybe this? =Nz(Sum([ActualsTot]),0)

Main form footer control value:

=Forms.MainFormName.SubFormName.Form.SubformFooterControlName

maybe this? =Forms.RegLocActCT3.TotMoAct.Form.ActualsTotFooter

Thanks. Trying your suggestion, familiar with Nz somewhat, but Still
struggling.

[quoted text clipped - 35 lines]

Mbr