MS Report - Sum Function

You can't embed a select statement like this.  Sum() works on the domain of the recordset.  If you want to sum something else, use the domain function - DSum("Amount", "yourtablename", "[Order Type] = "Capital")

However, I think you probably need more criteria than just "=capital".

Your right, needed more criteria.  

Trying this but it's giving me the total amount for the entire table.  Not filtering just the Asset items by Expense.

=DSum("Amount","PurchaseOrders",[Asset]="99992427" And [OrderType]="Expense")

The statement is not delimited correctly.  There are three arguments, the field you want to sum, the table or query that is the domaiin, and the criteria.

=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense'")

Notice how the criteris is all encapsulated into a string.  Getting the syntax correct when you need embedded quotes can be tricky.

Hard-coding the asset is probably the wrong approach.

no points please:

The syntax for the DSUM() would look something like:

=DSum("Amount","PurchaseOrders","[Asset]=99992427 And [OrderType]='Expense'")

of, if [Asset] is a string:

=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense'")

Dale

Pat and Dale

Your both right.  It does show the correct total for the Order Type of Capital or Expense.  But it's putting the total in both Groups.

If I try this I get an #Error
=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense' And ‘[OrderType]=’Capital’")

Correction - there are two groups, Capital and Expense.  It's putting the same total in both groups.  In this it's only identifying Expense, it should only put the amount in the Expense group.  However it's putting the correct amount in both the Capital and Expense group.  I still need to add the group Capital to the equation.

=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense'")

you have an extra ' before the [OrderType] field, try:

=DSum("Amount","PurchaseOrders","[Asset]='99992427' And [OrderType]='Expense' And [OrderType]=’Capital’")

oops, just saw that.

Getting an #Error.  Is it possible what I'm trying isn't possible?  Would there be a better way?

Guys,

I did a work around.  

I created another text field for Capital.  I needed these to do calculations in the report header.  I'll reference these two text boxes in the Header and simply hide the fields within the groups.

Thank you both.

You do realize that the Report header only ever prints ONCE at the beginning of the report.

Did you try to use sum over Group/All. Split Expense and Capital in detail section.
Setting the value in header comes next.

Here is a way using Sum Over Group/All

Almost identical report inserted as a subreport,, Visible=no,  just to make the total available to the main report..
Run b_report_total_per_group_in_header report.
b_report_total_per_group_in_header2 is the subreport.
report-per-group-total-in-header.accdb

Big thank you to all.  hnasr - wouldn't have gotten that!  Pat and Dale what you gave me kind of worked.  Adding and referencing other text fields did the trick.  

Thank you all..