DSUM with multiple criteria

[Bkper087]

I'm having issues with the DSUM function. I'm trying to use multiple criteria. Not sure what is happening, but its returning a number way too large. The database is a budget tracking tool. I have one query that includes all categories of expenses separated using check box indicators for budgeted, forecasted expenses, and actual expenses. I am trying to use DSUM on a form to show the total amount spent vs budgeted based on a combo box which the user selects the expense category from. In the expression below, I am trying to get the budgeted amount. All criteria should be numeric values. Any idea what might be wrong?



=DSum("[ExpenseTotal]","[qryExpense_Printing]","[EventIDfk]=" & [EventIDfk] And "[Budget]=" & [Budget] And "[CategoryIDfk]=" & [CategoryIDfk])

[aytee111]

=DSum("[ExpenseTotal]","[qryExpense_Printing]","[EventIDfk]=" & [EventIDfk] & " And [Budget]=" & [Budget] & " And [CategoryIDfk]=" & [CategoryIDfk])

[NTC]

as a111 post displays - the DSUM multi criteria scenario becomes very syntax sensitive. An alternative approach is to use a query instead, save it with a name. And then use a DLookUp to call in the value of the Query. Besides avoiding the nasty syntax one can run that query standalone in order to test and refine it.

[Bkper087]

I tried using the expression aytee111 wrote, but got a #Name error. I'm just going to use query's to reference.

[ssanfu]

So did you try debugging the expression?

Where are the values "EventIDfk", "Budget" and "CategoryIDfk" coming from? If they are from controls on a form, I would use "Me." and the control name
If the control name is "EventIDfk", then I would use Me.EventIDfk.

Start by using one criteria.
"EventIDfk" should be a number, so no delimiters needed

=DSum("[ExpenseTotal]","[qryExpense_Printing]","[EventIDfk]=" & Me.EventIDfk)

If this returns a value, add another criteria.

=DSum("[ExpenseTotal]","[qryExpense_Printing]","[EventIDfk]=" & Me.EventIDfk & " And [Budget]=" & Me.Budget )

If this returns a value, add another criteria.

=DSum("[ExpenseTotal]","[qryExpense_Printing]","[EventIDfk]=" & Me.EventIDfk & " And [Budget]=" & Me.Budget & " And  [CategoryIDfk]=" & Me.CategoryIDfk)

If, at any stage there is an error, fix it, then continue.