Logic in MTD/YTD (1 Viewer)


Local time
Today, 08:09
Sep 24, 2020
Hope someone can help me find clarity in an issue I am having with a dynamic report.

For each row of data, I am calculating the Current week, month to date (MTD) and year to date (YTD) values. This works fine where the same month and year are throughout the current week. However if I would have a week like this one where new month starts in middle it will still be showing last month's data as it goes by date commencing.
If I were to set MTD/YTD according to today's date, it would restrict to this month/year but the report is dynamic - user can select what week they wish to view.

The initial values of Week commence / MTD and YTD are set according to VBA values generated on load event.

Would appreciate if anyone can shed light on my dilemma.

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:09
Feb 19, 2002
Since the domains of the three calculations are different, it doesn't make much sense to do them in the same query. You would need to use three different domain functions. OR, you can use three separate queries. One each for WeekToDate, MTD, and YTD where you specify the criteria as a where clause. Then you join the three queries.

If you don't have a lot of data, it won't make much difference but as your recordset gets bigger, The three queries to do the calculation and a fourth query to join the three could easily be faster than the one query with the three domain functions. and more flexible also.


Banishment Pending
Local time
Today, 02:09
May 11, 2011
Can you give us some sample data to demonstrate your issue? Be sure to give us the edge cases as well as the dates immediately prior and after them to demonstrate your issue.

Users who are viewing this thread

Top Bottom