Results 1 to 7 of 7

Thread: Date Criteria

  1. #1
    Join Date
    Jun 2004
    Posts
    26

    Unanswered: Date Criteria

    If I run a query from say 1st February 2005 to Today's Date, I put a criteria of "Between 01/02/05 and Date()"

    This works fine.

    However, I want to run this alongside another query which provides the same data for the same period last year.

    So what would the command be if I wanted the same for last year?

    For example, If I run it today, the period covered by last year would be "01/02/04 to 03/03/04"
    If I run it next Friday, the period I'd want covered from last year would be "01/02/04 to 11/03/04"

    Does this make sense ?

    If not, please let me know and I'll try to provide better examples.

    Cheers.

    J.

  2. #2
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    just use Between Date1 And Date2.

    This should work fine.

  3. #3
    Join Date
    Jun 2004
    Posts
    26
    Thanks Cruickshanks.

    Trouble is, I will be running this report daily, and it would mean I have to change the query each day as opposed to it just working from my system date.

    J.

  4. #4
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Will you be using a list of set time periods in that case?

  5. #5
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    ok, for arguments sake, you want to review a report based on a year prior to the current date at the time the report is run, you need the following:

    Code:
        tmpDate = Date()
        newDate= DateSerial(year(tmpDate)-1, month(tmpDate), day(tmpDate))

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    This SQL statement worked fine:

    SELECT SomeDate
    FROM tblSomeTable
    WHERE ((SomeDate Between DateAdd("yyyy",-1,CDate("1/2")) And DateAdd("yyyy",-1,Date())));

    The CDate(1/2") will change automatically every year, and it will stop at the date you want. See the Help file for the DateAdd() function for details.

    Sam

  7. #7
    Join Date
    Jun 2004
    Posts
    26
    Cheers, I'll take a look.

    J.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •