RE: YTD and MTD math in a report.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks a lot I couldn't get any date math to work correctly,
but your idea worked great:

I am unable to get Month to Date working though. It keeps calculating the
total for last year's february.

Here is what I am trying:
=Sum(Abs(Month([Invoice_Date_List1])=Month(Date()))*[Total_Merchandise])

Appreciate the help,
JD

"Duane Hookom" wrote:

Are all values from all time periods in the record source of the report? If
so, you can sum an expression in a footer or header section with something
like:

Year to Date
=Sum(Abs(Year(SaleDate) = Year(Date())) * [SalesAmt])

You should be able to replace the:
Year(SaleDate) = Year(Date())
with other expressions for other time periods.

--
Duane Hookom
Microsoft Access MVP


"jdbit2byte" wrote:

(So I think I am going about this the right way, feel free to correct me if I
need to do my math in the query or elsewhere somehow.)

I am trying to make a YTD and MTD sales report.
This data is brought from 2 tables: tbl_Invoice tbl_Dealer
In table invoice there are all the purchases the dealers have made.
YTD MTD sales report means "What the dealers have bought from us"

I started with a listing with the dealers as the heading and each invoice
listed below them. Then I made an unbound box and had it sum up all the
invoice totals.

Now I can't seem to limit when the sum is summed. Meaning I have a total of
each dealers purchases but I can't split them up into "Last year" "This year"
"This month" "Last month"

Thanks,
JD




.

Relevant Pages

  • Re: Sum not recoginzing a negative number
    ... You cannot use normal floating point math with date/time values. ... >I have a report in which I am attempting to calculate elapsed time. ... In my report I group the two events by day and sum them ...
    (microsoft.public.access.reports)
  • Re: Standard Deviation
    ... is the sum of the divided by ... A differentiable function definitely makes the math more tractable. ... But standard deviation is usually used because either the physics ... If a simulation model matches the physics, ...
    (comp.dsp)
  • Re: how to sum time column using group by
    ... I need to sum a column using group by. ... In a Database DateTime field it ... performing math on them makes as much ... To store time intervals you should store them as integers that represent the ...
    (microsoft.public.access.queries)
  • Re: Using a calculated field for another calc
    ... The DCount and DSum ... The math I'm doing looks like it should be pretty easy. ... calculations. ... If you need to know the sum of Hours where =true, ...
    (microsoft.public.access.reports)