DSum for running total to date

[muttnut]

I'm about to go nuts trying to figure this out...I'm sure it's something pretty basic that I'm missing which will make me more nuts when someone provides the answer!

I'm trying to determine the week where the total paid for a given case reached or surpassed $1000. My query is called "DSUM_PRE" where I'm creating the payment week field. The fields in the query are CASE_ID, PYMT_WK, and PYMT_AMT. The code I'm using is

RUNNING_TTL: DSum("[PYMT_AMT]","DSUM_PRE ","[PYMT_WK]<=" & [PYMT_WK])

When I run the query "Running_Tt l" comes back null.

Please help before my laptop or I go flying off the roof...

[jforbes]

Try:

DSum("Nz([PYMT_AMT],0)","DSUM_PRE","[PYMT_WK]<=" & [PYMT_WK])

Nulls can cause your whole expression to return a Null

[muttnut]

Now I'm getting #Error...

[muttnut]

Based on your comment about the null values, I went to my "pre" query and put in a criteria to exclude anything <=0.00 and now I'm getting a $ result....unfor tunately it's the same $ amount for all cases...if I had to guess, I would say that it's giving me the grand total for all cases for each case and each payment month...getting closer!