Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Where Statement Using DateAdd

Discussion in 'Information Technology' started by Santara, Jul 28, 2006.

  1. Santara

    Santara
    Expand Collapse
    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
     
  2. Loading...


  3. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    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

    "Santara" <Santara@discussions.microsoft.com> wrote in message
    news:EF4521EE-4F08-4024-A80F-C53404A7FD10@microsoft.com...
    >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
     

Share This Page