# YTD and MTD math in a report.

J

#### jdbit2byte

(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

D

#### Duane Hookom

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.

J

#### jdbit2byte

Thanks a lot I couldn't get any date math to work correctly,

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 said:
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 said:
(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

D

#### Duane Hookom

Your expression was looking at the month only. You need to grab the month and
year like:

=Sum(Abs(Format([Invoice_Date_List1],"YYYYMM")=Format(Date(),"yyyymm"))*[Total_Merchandise])
--
Duane Hookom
Microsoft Access MVP

jdbit2byte said:
Thanks a lot I couldn't get any date math to work correctly,

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 said:
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 said:
(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

J

#### jdbit2byte

Thanks for the quick reply and extra help, this works great.

Duane Hookom said:
Your expression was looking at the month only. You need to grab the month and
year like:

=Sum(Abs(Format([Invoice_Date_List1],"YYYYMM")=Format(Date(),"yyyymm"))*[Total_Merchandise])
--
Duane Hookom
Microsoft Access MVP

jdbit2byte said:
Thanks a lot I couldn't get any date math to work correctly,

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 said:
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

:

(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