Access: when subform's query has no data. Creates an error message in main form

[Gran]

Hi, Im using Access 97, and I have two subforms which have a total for
the query'd data. I then go and total the two together on the main
form. But, when one has no data it brings up an error. I have searched
for the answer and tryed both these approachs...but dont work for me.
I am working in the control source of a text box.

=IIf(IsNull([sqry1].[Form]![txtSumUnit1]),IIf(IsNull([sqry2].[Form]![txtSumUnit2]),[sqry1].[Form]![txtSumUnit1],[sqry2].[Form]![txtSumUnit2])+[sqry1].[Form]![txtSumUnit1]),[sqry2].[Form]![txtSumUnit2])

or

=nz([sqry1].[Form]![txtSumUnit1],0)+ nz(sqry2].[Form]![txtSumUnit2],0)

Aparently these should work, but dont. Whatever Im doing wrong can
someone help?

thanks a million

Gran

[Allen Browne]

The expressions you have will not work if the subform has no records. In
that case, the subform displays completely blank, and any attempt to refer
to the non-existent text box in the subform naturally produces an error.

To fix that, you could make the subform's Recordset updatable or change its
AllowAdditions property to Yes. Alternatively you could examine the
RecordCount of its RecordsetClone, or test for IsError().

This kind of thing:

=IIf([sqry1].[Form].[RecordsetClone].[RecordCount] > 0, Nz([sqry1].[Form],
0), 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gran" <gr***********@bbc.co.uk> wrote in message
news:8b*************************@posting.google.co m...

Hi, Im using Access 97, and I have two subforms which have a total for
the query'd data. I then go and total the two together on the main
form. But, when one has no data it brings up an error. I have searched
for the answer and tryed both these approachs...but dont work for me.
I am working in the control source of a text box.

=IIf(IsNull([sqry1].[Form]![txtSumUnit1]),IIf(IsNull([sqry2].[Form]![txtSumU
nit2]),[sqry1].[Form]![txtSumUnit1],[sqry2].[Form]![txtSumUnit2])+[sqry1].[F
orm]![txtSumUnit1]),[sqry2].[Form]![txtSumUnit2])
or

=nz([sqry1].[Form]![txtSumUnit1],0)+ nz(sqry2].[Form]![txtSumUnit2],0)

Aparently these should work, but dont. Whatever Im doing wrong can
someone help?

thanks a million

Gran

[Grantham Good]

Thanks, fixed it through creating two text boxes and sperating the parts
of the expression that may cause an #error. Then, used
Iif(iserror([txt1]),0,[txt1])+Iif(iserror([txt2]),0,[txt2])... then made
txt1 and txt2 not visible.

and it worked. Just needed a fresh approach!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!