Dsum Issue

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