Dsum Issue

Originally Posted by dozingquinn

Hello,

Thanks to a previous helper I found some great Dsum info here:

http://www.mvps.org/access/general/gen0018.htm

Unfortunately I can't get the code to work for my requirements.

I'm trying to sum the payment received ($) field within my 'tbl_treatment payments' table for every record that has the field "payment method" = cash.

The code I'm using is:

I'm sure my mistake is something simple, however I can't for the life of me figure it out. It's driving me crazy.

This is all for a report in Access 2000.

Thanks!

Originally Posted by dozingquinn

Looking at the second example, I tried the apostrophe before and after the criteria - but alas it still didn't work.

=DSum("payment received","tbl_treatment payments","payment method= ‘cash’")

Payment method is a text field.

Originally Posted by dozingquinn

Thanks for the suggestion,

I tried the brackets - (both suggestions) - but I still return a #error value.

:(

Originally Posted by dozingquinn

Thankyou Puppy!

I now get a value! (Total cash sales across the whole record set). I see now (after reading up on NZ functions) that the 0's must have been causing issues.

As my report is grouped by month, is it possible to extend the formula so that it only dsum's the values within the given month?

Looking round I tried to decipher it myself - however my stab in the dark hasn't been fruitful:

=DSum(nz("[payment received]",0),"tbl_treatment payments","[Payment method]= 'cash'" AND “[Payment date]” = & 'MM())

Originally Posted by dozingquinn

Thanks again.

Nearly there...

When I indicated that my report was grouped by months I should have also clarified that the report autogenerates all the months. Is it possible to tie the month criteria to the month in question - rather than hardcoding it to a specific month?

Or am I going about this the wrong way?

Originally Posted by puppydogbuddy

If you have a FiscalMonth in your table, you can do something like this:
Try this syntax, (using February as an example).
=DSum(nz("[payment received]",0),"tbl_treatment payments","[Payment method]= 'cash'" AND Month(“[Payment date]”) = [FiscalMonth])

Originally Posted by FishVal

:-)

Nz function does happily nothing here.
nz("[payment received]",0) = "[payment received]" always

Anyway aggregate functions deal with Nulls pretty nice. Moreover replacing Nulls with 0's will have no result with DSum and will cause erroneous result with DAvg and DStDev.

Regards,
Fish

Originally Posted by puppydogbuddy

Fish,
Firstly......if the NZ function had no effect, go back and read posts #'s 4, 5, 6, and 7 and then explain to me how come the payments started appearing in the result set for the first time when the only change I made was to add the NZ function......

Originally Posted by FishVal

Apologizes, PDB, but do you want me to explain what might happening in OP's project?

Take a look at the expression.

=DSum(nz("[payment received]",0),"tbl_treatment payments","[Payment method]= 'cash'" AND Month(“[Payment date]”) = [FiscalMonth])

The first argument passed to DSum function is what is returned by Nz function and nz("[payment received]",0) will always return string "[payment received]". To make Nz to run on each record it is better to include it to string given to DSum as Expression argument.

=DSum("nz([payment received],0)", ...

But that is never needed as one of the purposes of Null is to let aggregate functions to treat a value as absent.



I wanted to state that Null values are actually friends of aggregate functions and never confuse them.

P.S. To make sure I'm not making a silly mistake I've tried
=DSum("fld1", "tbl1")
=DSum(Nz("fld1",0), "tbl1")
=DSum("Nz([fld1],0)", "tbl1")
in unbound form control
All them return the same. fld1 values are Null, 100, Null, 200

Originally Posted by dozingquinn

Hello again,

........
=DSum(nz("[payment received]",0),"tbl_treatment payments","[Payment method]= 'cash'") And Month([Payment date])="[real month]"

......

Originally Posted by FishVal

Explain please what you are referring to with [real month]?

Originally Posted by FishVal

[*] Table field, bound control, unbound control?

Originally Posted by FishVal

[*] What [real month] supposed to contain?

Originally Posted by dozingquinn

.......

=DSum(nz("[payment received]",0),"qry_treatment payments","[Payment method]= 'cash' And Month([Payment date])=" & [real month])

[Real month] was a new field created in the query that extracts the numbered month of the [payment date] field. By setting it up I was attempting to get records that returned a [payment method] = cash - and that had a [payment date] equal to each grouped month.
.......................
As explained above - the number of the month in [payment date]. e.g a [payment date] of 01/Feb/07 would return a [real date] of 2.

Thanks.