Dsum Issue

**MikeTheBike** · Oct 15 '07, 12:10 PM

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!

Hi

Looking at the site you references above, I suggest you look at the second line given

For numerical values:
DLookup("FieldN ame" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)
DLookup("FieldN ame" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldN ame" , "TableName" , "Criteria= #date#")

on the basis that 'cash' is a text field ??

MTB

**dozingquinn** · Oct 15 '07, 12:34 PM

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","paym ent method= ‘cash’")

Payment method is a text field.

**Lysander** · Oct 15 '07, 12:51 PM

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","paym ent method= ‘cash’")

Payment method is a text field.

You have spaces in your field names, so you need to enclose them in brackets

=dsum("[payment received]","tbl_treatmen t payments","[Payment method]='cash'")

You might have to put the table name in [ ] as well .

**dozingquinn** · Oct 15 '07, 10:26 PM

Thanks for the suggestion,

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

:(

**puppydogbuddy** · Oct 16 '07, 04:41 AM

Originally posted by dozingquinn

Thanks for the suggestion,

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

:(

Try it using the nz (null to zero) function like this:
=dsum(nz("[payment received]",0),"tbl_treat ment payments","[Payment method]= 'cash'")

**dozingquinn** · Oct 16 '07, 10:11 AM

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_treat ment payments","[Payment method]= 'cash'" AND “[Payment date]” = & 'MM())

**puppydogbuddy** · Oct 16 '07, 11:18 AM

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_treat ment payments","[Payment method]= 'cash'" AND “[Payment date]” = & 'MM())

Try this syntax, (using February as an example).
=DSum(nz("[payment received]",0),"tbl_treat ment payments","[Payment method]= 'cash'" AND Month(“[Payment date]”) = 2)

the 0's must have been causing issues

It isn't the zeros causing the issues, it is the nulls(where nothing is entered for payment). The nz function converts the nulls to zeros to resolve the issues.
Example:
null + $50 = null
0 +$50 = $50

**dozingquinn** · Oct 16 '07, 10:33 PM

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?

**puppydogbuddy** · Oct 16 '07, 11:12 PM

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?

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_treat ment payments","[Payment method]= 'cash'" AND Month(“[Payment date]”) = [FiscalMonth])

**FishVal** · Oct 16 '07, 11:46 PM

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_treat ment payments","[Payment method]= 'cash'" AND Month(“[Payment date]”) = [FiscalMonth])

:-)

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

**puppydogbuddy** · Oct 17 '07, 01:02 AM

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

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

Secondly, the OP is not using DAvg or DStDev, so that is not relevant here..

**FishVal** · Oct 17 '07, 01:38 AM

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

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

Secondly, the OP is not using DAvg or DStDev, so that is not relevant here..

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

**FishVal** · Oct 17 '07, 01:43 AM

To dozingquinn.

I suggest you before putting an expression to form/report control try to run it in VBA immediate window. This will give you a more comprehensive error description.

**puppydogbuddy** · Oct 17 '07, 03:40 AM

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_treat ment 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

Fish,
I understand what you are saying, what I don't understand is this:

Expression before NZ function:
=dsum("[payment received]","tbl_treatmen t payments","[Payment method]='cash'")

Comment from OP:
Thanks for the suggestion,
I tried the brackets - (both suggestions) - but I still return a #error value.

Expression with NZ function:
=dsum(nz("[payment received]",0),"tbl_treat ment payments","[Payment method]= 'cash'")

Comment from OP:
Thankyou Puppy!
I now get a value! (Total cash sales across the whole record set).