By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a

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!
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.