Dsum not working with continuous subform

F

FordTX

Hi,

I'm struggling with a Dsum formula and would greatly appreciate som
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 (Continuou
subform within the OrderEntryForm)

The two tables are linked through the fields called [ReqNumberREQ] i
the REQTable and [REQNumber] in the BudgetTracking table. (both field
are text fields)

In the BudgetTracking table there is a currency field calle
[OrderTotalSpend]. I would like to have a text box in the form heade
or footer that sums [OrderTotalSpend] for all records matching th
current [ReqNumberREQ] field.

I've tried this and the criteria is not being hit due to the tex
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!
 
K

Ken Snell [MVP]

This formula is the correct one to use when the REQNumber field is a text
field:

=DSum("[OrderTotalSpend]","BudgetTracking","[REQNumber] = '" &
[ReqNumberREQ] & "'")

If you're getting an #Error, then it's likely that your form is not finding
a control or field named ReqNumberREQ in the subform. Do you have such a
field/control there?
 
Top