Where Statement using DateAdd

[Guest]

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]

Hi Santara,

Between CDate("Jan/" & [Enter Year:]) And CDate("[Enter Month:] & "/" &
[Enter Year:])

will work. If you want it to prompt for the month before the year, change
it to:

Between CDate("[Enter Month:] & "/" & [Enter Year:]) And CDate("Jan/" &
[Enter Year:])

If your date data includes day, as well as month and year, this will only
give records for 1-Jan (note: your current criteria also limits to 1st of
entered month). You can get around this by using dateadd to add 1 month to
the entered month, then again to subtract 1 day, to give the last day of the
entered month:

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

HTH,

Rob

 

[Rob Parker]

Correction to explanatory text:
"... only give records to 1-Jan ... " should read "... only give records to
1st of entered month ..."
[It only gives records for 1-Jan if you enter Jan as the month - which is
what I did when testing!]

Rob

Hi Santara,

Between CDate("Jan/" & [Enter Year:]) And CDate("[Enter Month:] & "/" &
[Enter Year:])

will work. If you want it to prompt for the month before the year, change
it to:

Between CDate("[Enter Month:] & "/" & [Enter Year:]) And CDate("Jan/" &
[Enter Year:])

If your date data includes day, as well as month and year, this will only
give records for 1-Jan (note: your current criteria also limits to 1st of
entered month). You can get around this by using dateadd to add 1 month
to the entered month, then again to subtract 1 day, to give the last day
of the entered month:

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

HTH,

Rob

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

 

[John Spencer]

Take a look at the DateSerial function.

To get all data between January 1st of the current year and the end of a
designated month, you can use the following
Between DateSerial(Year(Date()),1,1) and DateSerial(Year(Date()),[Enter
Month Number]+1,0)

To get the corresponding period for the prior year.
Between DateSerial(Year(Date())-1,1,1) and DateSerial(Year(Date())-1,[Enter
Month Number]+1,0)