If query/subform is empty set calculation value to 0

[Tony]

Using a subform, I am summing a field across multiple records, then adding
multiple subform totals to provide a single total on the main form. However,
in some cases, some subforms may not return any records (Due to no records
existing for the item), and in this case, i get a #Error response, and the
rest of the calculations on the main form fails. How do I check to see of
the table is empty, and if so return a zero value, otherwise, return the sum
value being sought?

 

[Jeff Boyce]

Tony

Consider using queries to fill the forms/subforms. Then use the Nz()
function in your query to convert nulls to zeros.

Does that do it?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Douglas J. Steele]

Actually, it probably won't do it, Jeff, since the query will return an
empty recordset. Nz only works on Null values, not on values that don't
exist.

My article "Reporting what's not there" might help
http://www.databasejournal.com/features/msaccess/article.php/3844021/Reporting-whats-not-there.htm

 

[Jeff Boyce]

Good point, Doug.

There's a difference between Nulls, evidently <g>!

There's a field with nothing there = Null.

Nothing is there, not even a record ... ?!<> Null, but an empty recordset.

An "empty recordset" sounds so much more appealing than "nothing there"

Regards

Jeff B.

 

[James A. Fortune]

Good point, Doug.

There's a difference between Nulls, evidently <g>!

There's a field with nothing there = Null.

Nothing is there, not even a record ... ?!<> Null, but an empty recordset.

An "empty recordset" sounds so much more appealing than "nothing there"

Regards

Jeff B.

The Nz() function can be used around a subquery. Something like:

SELECT VID, Nz((SELECT Sum(A.theValue) FROM tblValues AS A WHERE
A.theDate >= #1/1/10#), 0) + Nz((SELECT Sum(B.OtherValue) FROM
tblValues AS B WHERE B.theDate >= #1/1/10#), 0) As theSum FROM
tblValues;

If the subquery contains no records, the Nz() function will treat the
subquery like a Null. That should keep a Null value in one of the
subqueries from annihilating a value from the other subquery through
Null propagation. Note: The example is meant to illustrate the
concept only and is not meant to suggest that the SQL is useful for
any purpose as shown.

James A. Fortune
(e-mail address removed)