DSum to return previous change totals for each change number

[Todd H]

I am trying to calculate a running sum that calculates the previous
changes for any current change for each contract. I think the DSum is
the best route, but I cannot get the expression to filter the correct
records; currently it totals all the records.

The function should return the sum of the [PCOFinalCost] for the
previous [PCCO_No]'s for each different [PTCContractID] unless the
[PCCO_No] is 1. If it is the first PCCO, then the previous change
total is 0.

Here is the expression:

=IIf([PCCO_No]=1,0,
(DSum("PCOFinalCost","qryPCCOData","[qryPCCOData.PTCContractID] = _
'[PTCContractID]'" And "[qryPCCOData.PCCO_No] between '1' and
'([PCCO_No]-1)'")))



Any help would be appreciated

 

[Duane Hookom]

Try something like:

=IIf([PCCO_No]=1,0, DSum("PCOFinalCost", "qryPCCOData",
"PTCContractID = '" & [PTCContractID] & "' And PCCO_No Between 1 and " &
[PCCO_No]-1 ))

This is assuming PTContractID is text and PCCO_No is numeric.

 

[Todd H]

Great! Worked like a champ. Thanks for the quick response!

Todd