I have a quick question. In Excel, there is a formula where you can sum certain cells based on certain criteria. I would like to do the same in a report that I have in Access. I want to find the absolute sum of a field IF the order has shipped, otherwise, I want the report to show a 0 in that field. I have tried using the DSum function, but I can't get that to work.
Sum If in Access Reports
- Thread starter LadyDi
- Start date
CraigDolphin
GrumpyOldMan in Training
- Local time
- Yesterday, 18:59
- Joined
- Dec 21, 2005
- Messages
- 1,582
In the query which you report is bound to have a field like:
Subtotal: IIf([HasShipped]=-1,[Yourfield],0)
(you'll need to modify the conditional statement depending on what values are stored in the field which indicates whether the 'something' has shipped or not, and obviously change the field names to suit your actual field names)
In your report, have a textbox control in the detail area that is bound to the Subtotal field. Then have an unbound textbox control in the report footer with a control source
=Sum([Subtotal])
This should total up all the subtotals shown in the report. You can also place the total control in different group footers if you want to sum up at different grouping levels than the entire report.
Subtotal: IIf([HasShipped]=-1,[Yourfield],0)
(you'll need to modify the conditional statement depending on what values are stored in the field which indicates whether the 'something' has shipped or not, and obviously change the field names to suit your actual field names)
In your report, have a textbox control in the detail area that is bound to the Subtotal field. Then have an unbound textbox control in the report footer with a control source
=Sum([Subtotal])
This should total up all the subtotals shown in the report. You can also place the total control in different group footers if you want to sum up at different grouping levels than the entire report.
