Where Statement Using DateAdd

[Santara]

I have a report that compares the current full past year’s data to the
previous full past year’s data based on the selected month for the report.

Example: JUNE 2005 Report

Current Full Year JUNE 2005 includes July 2004 thru June 2005
COMPARED TO Previous Full Year JUNE 2004 which includes July 2003 thru June
2004

Following is the WHERE statement that is used to pull the data for the query
used by the report on the MonthYear field. This query is run twice by the
report; once to show the current years data and a second time for a subreport
to show the prior years data.

Between DateAdd("m",-11,CDate([Enter Month:] & "/" & ([Enter Year:]))) And
CDate([Enter Month:] & "/" & [Enter Year:])

The formula uses parameters to enter the current year and month and the
prior year and month.

The formula currently does one full year back from the requested Month and
Year, and the Prior year’s full year back from the requested Month and Year.

However, we now want to go back only to January of the current year and pull
forward to the Requested Month, and back to the January of the prior year and
pull forward to the Requested Month.

Example: APRIL 2006 Report

Current Full Year APRIL 2006 includes January 2006 thru April 2006
COMPARED TO Previous Full Year APRIL 2005 which includes January 2005 thru
April 2005

The formula needs to work for all upcoming months and years as well. That
way I won’t need to remember to fix it for 2007, 2008, etc… We will continue
to use the parameters for the user to enter the Month and Year information.

Thank you for your help!

Santara

 

[Rob Parker]

Answered in microsoft.public.access.queries.

Please do NOT multipost. If you must post to more than one group (and that
is rarely needed here), then cross-post, so that an answer posted in one
group will appear in all the groups you chose.

Rob