Advice on year to date criteria

[Freddie]

I have a table with a currency column and a date column (mm/dd/yyyy). Every
year I would like to calculate the currency column and report this on a
year-to-date report. How can I enssure that when I run the report I get the
values for the current year to date regardless of how many years I have
stored in the date column.

Freddie.

 

[Allen Browne]

Do not store the YTD amount for each client in your client table.
Instead, calculate it when needed.

Create a query into your client table.
Type something like this into a fresh column of the Field row:

YTD: ( SELECT Sum(Amount) As YTD
FROM tblInvoice
WHERE (tblInvoice.ClientID = tblClientID)
AND (tblInvoice.InvoiceDate >= DateSerial(Year(Date()), 1,1) )

This subquery asks for the sum of the Amount column in the tblInvoice table,
where the client is the one in the main query, and the invoice date is this
year.