DSum and date as criteria

A

Andreas

Hello all,

I am calculating a running sum within a query using the DSum function.

DSum("[sngAmount]";"[tblAccountMovements]";"STR(CDBL([datDate]))<=" &
Str(CDBL([datDate])))

sngAmount contains positve or negative numbers from the table
"tblAccountMovements".
"datDate" is a date field from the same table.

I would like to calculate a running total within a query that is
sorted by "datDate". Basically, the above formula works fine. However,
there arise problems if datDate contains a date several times. If this
is the case, the calculation in the query gives me the total for the
whole day in every record with the specific date.

Here's an example:

tblAccountMovements

datDate sngAmount
01.01.09 1000
15.02.09 -200
30.06.09 5000
30.06.09 -2000

The result looks like this:

datDate sngAmount Running total
01.01.09 1000 1000
15.02.09 -200 800
30.06.09 5000 3800 (this should be 5800)
30.06.09 -2000 3800

The third row should give me a value of 5800, the rest is fine.
Instead, the third row is already the sum of the two numbers for the
30.06.09 (5000-2000=3000).

How am I supposed to adjust the formula to incorporate different
values for the same date?

Regards,
Andreas
 
D

Duane Hookom

Keeping in mind that records are like marbles in a box (no particular order)
how would you determine which movement occured first on June 30?
 
J

John W. Vinson

Hello all,

I am calculating a running sum within a query using the DSum function.

DSum("[sngAmount]";"[tblAccountMovements]";"STR(CDBL([datDate]))<=" &
Str(CDBL([datDate])))

sngAmount contains positve or negative numbers from the table
"tblAccountMovements".
"datDate" is a date field from the same table.

I would like to calculate a running total within a query that is
sorted by "datDate". Basically, the above formula works fine. However,
there arise problems if datDate contains a date several times. If this
is the case, the calculation in the query gives me the total for the
whole day in every record with the specific date.

Here's an example:

tblAccountMovements

datDate sngAmount
01.01.09 1000
15.02.09 -200
30.06.09 5000
30.06.09 -2000

The result looks like this:

datDate sngAmount Running total
01.01.09 1000 1000
15.02.09 -200 800
30.06.09 5000 3800 (this should be 5800)
30.06.09 -2000 3800

The third row should give me a value of 5800, the rest is fine.
Instead, the third row is already the sum of the two numbers for the
30.06.09 (5000-2000=3000).

How am I supposed to adjust the formula to incorporate different
values for the same date?

Do you have some other field, such as an Autonumber, which would let you
distinguish different records from the same date?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dsum and Dmax with structured references 1
dsum - 1
dsum sytax error 5
dsum 9
DSUM Function 2
DSUM Date Criteria 3
Dsum Problem with date criteria 1
Running Total 10

Top