DSum with Multiple Critera in a Query

[flamingphoenix]

Hello,



I'm a novice to Access and using Access 2010.

I'm trying to create a DSum formula in a query that sums the positive values in [AllocationRemaining] (and ignores the negative values] for each ProductID.

ProductID = number
AllocationRemaining = number
Both fields are stored in a query called RemainingAllocation

I've tried various different amendments but I can't work out the correct way to phrase the ProductID part of my criteria. My latest attempt was:

DSum("[AllocationRemaining]","RemainingAllocation","[AllocationRemaining]>0 ' " & [ProductID] & " ' ")

I know the first three parts work as if I remove the ProductID criteria, it will sum the positive values in the entire sheet. However I'm really struggling with the how to express that criteria (probably because I'm just adapting other formulas that I've seen in other threads)

Any help would be greatly appreciated!!

Thanks
Liz

[NTC]

make a calculated field in your query
pAR: iif([AllocationRemaining]>0,[AllocationRemaining],0)

then sum the pAR field