Summing data

[Rob]

I need to sum data from two subforms on my main form.

main form sum text box is: (txtBalance)
=[Forms]![frmMain]![fsubFirst]![txtTotal].[Value]-[Forms]!
[frmMain]![fsubSecond]![txtTotal].[Value]

subform 1's sum text box is: (txtTotal)
=Sum([curAmount])

subform 2's sum text box is: (txtTotal)
=Sum([curAmount])

This works great if there is data in both subforms. If
these is no data in either or both forms I get #Error in
the main form's txtBalance box.

I've tried:
=IIf([Forms]![frmMain]![fsubFirst]![txtTotal].[Value] Is
Null,0,[Forms]![frmMain]![fsubFirst]![txtTotal].[Value])-
IIf([Forms]![frmMain]![fsubSecond]![txtTotal].[Value] Is
Null,0,[Forms]![frmMain]![fsubSecond]![txtTotal].[Value])

but that doesn't work.

How can I make txtBalance equal 0 when there is no data in
one of the subforms?

Thanks.

 

[Vadim Rapp]

R> How can I make txtBalance equal 0 when there is no
R> data in one of the subforms?

I didn't try it, but consider function nz, i.e. nz(mycontrol.value, 0)

Vadim

 

[Rob]

Wish that worked, but the Nz function if virtually the
same as =IIf(mycontrol.value Is Null, 0, mycontrol.value)

When I use either Nz of IIf and the subform doesn't
contain any records the txtTotal box on the subform in
empty. I am not able to call its properties in the
immediate window either. It appear as if it does not exist
even though access displays the box.

If I use: =IIf(mycontrol.value Is Null, 0,
mycontrol.value) and there are no records in the subform,
I get #Error in txtBalance.

But if I use =IIf(mycontrol.value Is Not Null, 0, 1) I get
0 in txtBalance. This suggests to me that the txtTotal
does not exist even to create a null value.

I have also tried using Nz and IIf in the txtTotal box to
get that box to show 0 so I would have a value there that
the txtBalance box could add together.

I'm at a loss here.

 

[Rod Scoullar]

Rob,

You can't use 'Value Is Null' in an Access expression, only in a SQL
statement. You have to use IsNull(Value) instead.

I'm surprized the Nz function didn't work. That would have been my starting
point too.

Rod.