Attn: Doug Steele - Most Accumulated Value

  • Thread starter Thread starter wisemax
  • Start date Start date
W

wisemax

This query

SELECT [Cash Paid].TransactionDate, [Cash Paid].CashPaidAmount,
DSum("[CashPaidAmount]","Cash Paid","TransactionDate <=" &
Format([TransactionDate],"\#mm\/dd\/yyyy\#")) AS Accumulated
FROM [Cash Paid]
WHERE ((([Cash Paid].TransactionDate) Is Not Null And ([Cash
Paid].TransactionDate)<=[Forms]![BS Dialog]![EndingDate]));

generates this result

Date Paid Cash Paid Amount Accumulated
2/1/1997 $500.00 1200
3/1/1997 $800.00 2000
11/5/1996 $700.00 700

I want to pull out the most recent Accumulated as an entry to Balance
Sheet. In this case, it is $2,000

I could not get this done in this query. So I tried to build second
query that looks like this
SELECT IIf([TransactionDate]=Max([TransactionDate]),[Accumulated]) AS
CurrentCashPaidAmount
FROM [BS Cash Paid];

It simply don't work.

Can you help?
 
If all you want is the final accumulation, you don't need a query for that:
a simple DSum should suffice.

DSum("CashPaidAmount", "[Cash Paid]", "TransactionDate Is Not Null And
TransactionDate <= " & Format([Forms]![BS Dialog]![EndingDate],
"\#mm\/dd\/yyyy\#"))
 
Back
Top