Hi,
I'm struggling with a Dsum formula and would greatly appreciate some help.
2 Tables
- REQTable
- BudgetTracking
2 Queries
- OrderEntryBudgetQuery <-- connected to BudgetTracking table
- OrderREQQuery <-- Connected to REQTable
2 Forms
- OrderEntryForm <-- connected to OrderREQQuery
- OrderEntrySubform <-- connected to OrderEntryBudgetQuery (Continuous subform within the OrderEntryForm)
The two tables are linked through the fields called [ReqNumberREQ] in the REQTable and [REQNumber] in the BudgetTracking table. (both fields are text fields)
In the BudgetTracking table there is a currency field called [OrderTotalSpend]. I would like to have a text box in the form header or footer that sums [OrderTotalSpend] for all records matching the current [ReqNumberREQ] field.
I've tried this and the criteria is not being hit due to the text identifier quotes missing so it basically sums all rows
=DSum("[OrderTotalSpend]","BudgetTracking","[ReqNumberREQ]=" & [ReqNumberREQ])
I think this formula is more accurate, but I keep getting an #Error
=DSum("[OrderTotalSpend]","BudgetTracking","[REQNumber] = '" & [ReqNumberREQ] & "'")
Any help would be greatly appreciated!!
I'm struggling with a Dsum formula and would greatly appreciate some help.
2 Tables
- REQTable
- BudgetTracking
2 Queries
- OrderEntryBudgetQuery <-- connected to BudgetTracking table
- OrderREQQuery <-- Connected to REQTable
2 Forms
- OrderEntryForm <-- connected to OrderREQQuery
- OrderEntrySubform <-- connected to OrderEntryBudgetQuery (Continuous subform within the OrderEntryForm)
The two tables are linked through the fields called [ReqNumberREQ] in the REQTable and [REQNumber] in the BudgetTracking table. (both fields are text fields)
In the BudgetTracking table there is a currency field called [OrderTotalSpend]. I would like to have a text box in the form header or footer that sums [OrderTotalSpend] for all records matching the current [ReqNumberREQ] field.
I've tried this and the criteria is not being hit due to the text identifier quotes missing so it basically sums all rows
=DSum("[OrderTotalSpend]","BudgetTracking","[ReqNumberREQ]=" & [ReqNumberREQ])
I think this formula is more accurate, but I keep getting an #Error
=DSum("[OrderTotalSpend]","BudgetTracking","[REQNumber] = '" & [ReqNumberREQ] & "'")
Any help would be greatly appreciated!!