471,078 Members | 843 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,078 software developers and data experts.

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

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
2 2799
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

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.

Similar topics

7 posts views Thread by Julia Baresch | last post: by
49 posts views Thread by Yannick Turgeon | last post: by
20 posts views Thread by Deano | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.