total

  • Thread starter Thread starter ma
  • Start date Start date
M

ma

I have a date header and footer in my report. I have two
fields in my footer that I want to calculate a total, 1
works my other doesn't.
Is sums the total for 1 month, this is what I want.
Working total - "Monthly Expense"=Sum([amount])
Not working - "Monthly Cash Expense"=DSum
("[Amount]","[tbl_monthly_exp]","[purchases]='Less Cash'")
This gives me a total for both months that I have
entered. I want the total for 1 month like my "Monthly
Expense"

Thanks,
MA
 
ma said:
I have a date header and footer in my report. I have two
fields in my footer that I want to calculate a total, 1
works my other doesn't.
Is sums the total for 1 month, this is what I want.
Working total - "Monthly Expense"=Sum([amount])
Not working - "Monthly Cash Expense"=DSum
("[Amount]","[tbl_monthly_exp]","[purchases]='Less Cash'")
This gives me a total for both months that I have
entered. I want the total for 1 month like my "Monthly
Expense"
Click to expand...

Standard aggregate functions (Sum, Max, Min, etc.) have a sort of "context
sensitivity" to them. The records they aggregate on depends a great deal
on where you use them. Domain aggregate functions (DSum, DMax, DMin, etc.)
do not have this. Their context is contained within the arguments supplied
and they return the same value regardless of where you use them.

In your case you need to modify the criteria argument "[purchases]='Less
Cash'" so that it also filters on the current month being processed in the
report.
 
How do I modify the criteria to include date? I am still
in the learning stages.
Thanks,
MA
-----Original Message-----
I have a date header and footer in my report. I have two
fields in my footer that I want to calculate a total, 1
works my other doesn't.
Is sums the total for 1 month, this is what I want.
Working total - "Monthly Expense"=Sum([amount])
Not working - "Monthly Cash Expense"=DSum
("[Amount]","[tbl_monthly_exp]","[purchases]='Less Cash'")
This gives me a total for both months that I have
entered. I want the total for 1 month like my "Monthly
Expense"
Click to expand...

Standard aggregate functions (Sum, Max, Min, etc.) have a sort of "context
sensitivity" to them. The records they aggregate on depends a great deal
on where you use them. Domain aggregate functions (DSum, DMax, DMin, etc.)
do not have this. Their context is contained within the arguments supplied
and they return the same value regardless of where you use them.

In your case you need to modify the criteria argument "[purchases]='Less
Cash'" so that it also filters on the current month being processed in the
report.



--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Click to expand...
 
How do I modify the criteria to include date? I am still
in the learning stages.
Thanks,
Click to expand...

Alternate syntax used to better handle line-wrapping...

=DSum (
Expr: = "[Amount]", _
Domain: = "[tbl_monthly_exp]", _
Criteria: = "[purchases]='Less Cash' And Month(DateField) = " &
Me!MonthValue & "")

The above assumes that you have a control (MonthValue) on the report that
contains the month number for the current grouping. This control can be
hidden if you like.
 
Back
Top