Criteria to specify range of months and YTD (1 Viewer)

[accessaspire219]

I have a query which has a column called period. The period column contains months (mmm/yyyy) and year (yyyy YTD) for the data that the query generates.

Jan-09


Feb-09


Mar-09


Apr-09


May-09


Jun-09


Jul-09


Aug-09


Sep-09


Oct-09


Nov-09


Dec-09

2009 YTD
​

I want to define a criteria for this query so that the user can specify a range of months that he wants to the data for along with the YTD total. (For the type of calculation required, YTD cannot be calculated from the monthly result so they have to be two separate calculations)
Now, I have specified the criteria as Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]
However this criteria does not include the YTD records since do not fall in the range. I would like to define a criteria so that it displays the YTD based on the year of the EndDate for example:
Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate] And Like [EndDate](“yyyy”)
Does any know how this can be done?
Any help is appreciated!
Thanks!

 

[ajetrumpet]

have you check this out...http://www.access-programmers.co.uk/forums/showthread.php?t=157264???

 

[accessaspire219]

That is helpful, particularly the year-to-date formula - but I will need to merge the year-to-date formula with the date range formula to use on the same column. Is there a way to do that?

 

[ajetrumpet]

That is helpful, particularly the year-to-date formula - but I will need to merge the year-to-date formula with the date range formula to use on the same column. Is there a way to do that?

i have no idea. would have to see what you're working with to answer realyy...

 

[boblarson]

I would say to do this in two stages. Use one query to get the date range and another to get the YTD info and then do a UNION query up.