Report to show MTD and YTD $$

[claudia]

Need to generate a report that will show Month to Date and Year To Dat
sales
Table Fields
Customer
Product
Invoice Total
Salesman

I've made a query and can report current month OR any combination
ie Jan-Feb-Mar.... but cannot seem to figure out how to have tw
totals... current month and year to date show up on my query/report

THANK YO

 

[Marshall Barton]

Need to generate a report that will show Month to Date and Year To Date
sales
Table Fields
Customer
Product
Invoice Total
Salesman

I've made a query and can report current month OR any combination
ie Jan-Feb-Mar.... but cannot seem to figure out how to have two
totals... current month and year to date show up on my query/report

You're missing some information here. That table doesn't
seem to have a date field to use in those calculations.

If you have a query that works for month to date, it would
help if we could see it. I suspect that the same kind of
query or a modification of it will be able to do what you
want.

More information about the Sorting and Grouping in your
report is also important to this question.

 

[claudia]

OOPS>>> I do have a date field named PostMonth...it's the date we pos
the sale formatted mmmyyyy
I have been successful with YTD...using this expression:
04YTDSales: Sum(Abs(Year([PostMonth])=(2004))*[CommVolume])

Can I modify it for Month to Date

 

[Marshall Barton]

OOPS>>> I do have a date field named PostMonth...it's the date we post
the sale formatted mmmyyyy
I have been successful with YTD...using this expression:
04YTDSales: Sum(Abs(Year([PostMonth])=(2004))*[CommVolume])

Can I modify it for Month to Date?

Well, that's not the way I would have approached it, but,
yes, you can do that in one calculated field along with
this in another field:

MTDSales: Abs(Sum((Year(PostMonth)=2004 AND
Month(PostMonth) = 5) * [CommVolume]))

 

[claudia]

WOW... THANKS! I've been trying different ways off & on for tw
months... glad I asked... Just have one more question... MTD does no
recognize negative numbers... YTD expression works great.. my sums ar
correct... but MTD sums are incorrect... query shows negative number
as positive...
THANK YOU AGAI

 

[claudia]

WOW... THANKS! I've been trying different ways off & on for tw
months... glad I asked... Just have one more question... MTD does no
recognize negative numbers... YTD expression works great.. my sums ar
correct... but MTD sums are incorrect... query shows negative number
as positive...
THANK YOU AGAI

 

[claudia]

SUCCESSS... I must have had a ( in the wrong spot or one too many.
THANK YOU THANK YOU THANK YOU !!!

One more question & I'll be all set.... Expression to repor
CommVolume for a series of months... ie JAN-FEB-MAR...
Thanks
Claudi

 

[Guest]

Add a DatePart expression to the query (Example: Week #:
DatePart("ww",[field name]). When you create the report,
put the expression in a group for header or footer.

 

[Marshall Barton]

WOW... THANKS! I've been trying different ways off & on for two
months... glad I asked... Just have one more question... MTD does not
recognize negative numbers... YTD expression works great.. my sums are
correct... but MTD sums are incorrect... query shows negative numbers
as positive...

Sorry, I changed what you had a little too much. This
should take care of it:

MTDSales: Sum(Abs(Year(PostMonth)=2004 AND
Month(PostMonth) = 5) * [CommVolume])

 

[Marshall Barton]

One more question & I'll be all set.... Expression to report
CommVolume for a series of months... ie JAN-FEB-MAR...

You can use any valid comparison in that expression. Here's
an example for a range of dates:

RangeSales: Sum(Abs(PostMonth Between #1/1/2004# And
#3/31/2004#) * [CommVolume])

You never did say how you're fiddling the report or query to
change those year, month and range values, but be aware that
you could use popup prompts or reference controls on a form
in the query.