sum with some emptyfields

[David M via AccessMonster.com]

I want to put a Sum in a report footer for a field that either has currency
data and or is empty for each record.

I could easily replace the empty data with zeros but the user of the report
wants to have those records show nothing rather than zeros for easier
readability.

I tried =Sum(Nz([field],0)) but it doesn't work (still get the 'type
mismatch' error that is caused by the empty/null values)

Any way to write the sum controlsource so it counts the empties as zeros?

Thanks in advance,
David

 

[Allen Browne]

You do not need to take any action to sum a column on a report, even if
there are nulls.

Assuming a field named Amount, the text box in the report footer would have
these properties:
Control Source =Sum([Amount])
Format Currency

 

[OfficeDev18 via AccessMonster.com]

Sorry, but you're not dealing with a numeric field. If the type for that
field were numeric, the '0' would show up no matter what the user wanted.
You're dealing with a string/text field. It's also possible that your 'blank'
fields are not blank at all, but contain space(s). The type mismatch error is
nothing more than the error you get from trying to perform a mathematical
function (such as Sum()) on a non-numeric field.

Try the CSng() function, which converts string data to a single (see the
function in the Help file). Your proper syntax, then, would be

=Sum(CSng(Nz([FieldName],"0")))

Don't forget the quotes around the "0". CSng will only convert numbers that
are in string format, not spaces.

HTH

I want to put a Sum in a report footer for a field that either has currency
data and or is empty for each record.

I could easily replace the empty data with zeros but the user of the report
wants to have those records show nothing rather than zeros for easier
readability.

I tried =Sum(Nz([field],0)) but it doesn't work (still get the 'type
mismatch' error that is caused by the empty/null values)

Any way to write the sum controlsource so it counts the empties as zeros?

Thanks in advance,
David

 

[Allen Browne]

This information is incorrect. Number fields can be Null.

 

[David M via AccessMonster.com]

Thank you both for your time.

HTH - I also believe you are wrong. The fields are defnitely numeric
(formatted as currency as it happens) and, perhaps because the report source
is a union query, do contain empty/null values.

Allen - I do get a 'type mismatch' error when the field being sum'ed includes
empties, and not when I replace them with zeroes. Might it matter that the
report source is a union query, and the empties are being created by merging
numeric data with empty records using two single quotes ( '' )? Should I
use Null in the sequel instead?

Thanks,
David

 

[Allen Browne]

Yes! The presence of an empty string in the source query forces Access to
treat the column as text. You must use Null instead of the empty string in
your expression.

Access can still misunderstand the data type if this is a calculated field,
so you may need to typecast the expression as described here:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

In a UNION query, Access determines the data type of the column from the
first SELECT statement. If the first table does not have a particular field,
you typically use:
SELECT Field1, Null As Field2 FROM
in which case JET has no idea of the data type for Field2 so will always use
Text. If that is part of your issue, you can work around that with:
a) Swap the SELECTs around in the UNION, so the field type is present, OR
b) Use:
SELECT Field1, IIf(True, Null,0) As Field2 FROM
Field2 will always be Null, but the presence of the alternative is enough
for JET to figure out it's a number field.

(BTW, HTH is an acronymn for "hope that helps.")

 

[David M via AccessMonster.com]

SELECT Field1, IIf(True, Null,0) As Field2 FROM
Field2 will always be Null, but the presence of the alternative is enough
for JET to figure out it's a number field.

That's exactly what I needed to know. Thank you.

(BTW, HTH is an acronymn for "hope that helps.")

By The Way, THANKS! I'm an idiot!