Dsum Issue

[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:

=DSum("payment received","tbl_ treatment payments","paym ent method = cash")

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!

[MikeTheBike]

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]

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]

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]

Thanks for the suggestion,

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

:(

[puppydogbuddy]

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]

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]

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]

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]

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])