Re: Where Statement using DateAdd

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

"Santara" <Santara@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EC03AC6D-6960-4E66-A9F5-3E7A4B1D5E94@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Query records with single Dates Automatically
    ... Here is the code for the Out of Town Report: ... both queries pull from the same table. ... -- Two Tables Attendees and Visits ... Please copy and post the SQL of your query. ...
    (microsoft.public.access.queries)
  • Re: Year-To-Date Query with a lime Twist
    ... DateSerialexpressions to explore what it actually was doing, ... enter the month and year of the report period ie 02/2007 and let it fly). ... When I copied and pasted your info into my query and replaced the ... 01/01/2007 the query does not pull the the last days of the month for ...
    (microsoft.public.access.queries)
  • RE: Help needed with Access 2000 Reports
    ... Pull that into your query. ... the Employee ID in here] or something like that. ... It will then only pull that one record. ... Now go back to your report and change your fields to pull from this new ...
    (microsoft.public.access.reports)
  • RE: Newbie question
    ... Use one query with the date criteria to pull by date. ... >> I finally made a form to input this data and can print a report of this data. ...
    (microsoft.public.access.gettingstarted)
  • Re: Report that pulls data from 6 tables
    ... you run the query in design mode, ... "KARL DEWEY" wrote: ... I am not the best with reports so my first attempt was to pull the ... what it is that you're attempting to display in your report. ...
    (microsoft.public.access.reports)