DSum and date as criteria

[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

 

[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?

 

[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?

 

[Tom Wickerath]

Hi Andreas,

Try creating the odometer example, and then see if you can apply the same
logic to your application. I recommend using Method 2.

Referring to a Field in the Previous Record or Next Record
http://support.microsoft.com/kb/210504

Note: Disregard the "Acc2000" in the title of this KB article.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________