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","payment 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("FieldName" , "TableName" , "Criteria = n")

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

For dates:
DLookup("FieldName" , "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","payment 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","payment 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_treatment 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_treatment 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_treatment 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_treatment payments","[Payment method]= 'cash'" AND “[Payment date]” = & 'MM())

Try this syntax, (using February as an example).
=DSum(nz("[payment received]",0),"tbl_treatment 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_treatment payments","[Payment method]= 'cash'" AND Month(“[Payment date]”) = [FiscalMonth])

[FishVal]

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

:-)

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]

:-)

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]

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

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]

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]

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

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

Expression before NZ function:
=dsum("[payment received]","tbl_treatment 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_treatment payments","[Payment method]= 'cash'")

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

[FishVal]

That is a classic illusion. You are trapped with what you see. Sure nobody but the OP can explain this.

[dozingquinn]

Hello again,

A big thanks to Puppy & Fish for trying to help me out - all much appreciated. I don't want to buy into any argument, however my original dsum statement wasn't working without adding the nz value.

I have since tried the suggestion:

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

_where [real month] is essentially another field with the expression month([payment date]).

Unfortunately this didn't work. Infact it gave a zero sum. Taking out the AND Month... returns a value which dsums the whole record set again.

Is there an easier method in just trying to Dsum the criteria for all months - without having to hardcode the month value?

[FishVal]

Hello again,

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

......

Hi, dozingquinn.

Actually you are going with a very strange syntax.
At least it should look like

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

To suggest you an exact syntax I need answers for the following questions:

• Explain please what you are referring to with [real month]?
• Table field, bound control, unbound control?
• What [real month] supposed to contain?
• Month number, month name or abbreviation?

[dozingquinn]

Hi FishVal - I forgot to note in my previous code that I'm now working off a query - rather than a table source:

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

Explain please what you are referring to with [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.

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

I originally set the report up to source from [tbl_treatment payments], however as I had to create a [real month] field - I changed the record source to [qry_treatment payments].

All fields are bound to the query source

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

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.

[FishVal]

.......

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

????

You retrieve Month([Payment date]) as [real month] in query [qry_treatment payments], then you compare Month([Payment date]) and [real month] in filter argument of DSum function.

So what are you expecting from that comparison?