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\#"))


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>