DSum to return previous change totals for each change number

  • Thread starter Thread starter Todd H
  • Start date Start date
T

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
 
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.
 
Back
Top