sum with some emptyfields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David M - 30 Dec 2005 16:18 GMT

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

Reply to this Message

Allen Browne - 30 Dec 2005 17:20 GMT

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

Signature

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.

>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.
[quoted text clipped - 11 lines]
> Thanks in advance,
> David

Reply to this Message

OfficeDev18 - 30 Dec 2005 17:26 GMT

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.
[quoted text clipped - 10 lines]
>Thanks in advance,
>David

Signature

Sam

Reply to this Message

Allen Browne - 30 Dec 2005 17:31 GMT

This information is incorrect. Number fields can be Null.

Signature

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.

> 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.

Reply to this Message

David M - 30 Dec 2005 18:34 GMT

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

Reply to this Message

Allen Browne - 31 Dec 2005 02:58 GMT

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.")

Signature

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.

> Thank you both for your time.
>
[quoted text clipped - 14 lines]
> Thanks,
> David

Reply to this Message

David M - 31 Dec 2005 03:22 GMT

> 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!

Reply to this Message