Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: How do I write in a date range?

    I'd like to write a function that automatically runs a 12-month rolling report (rather than supplying a specific start and end date). In Crystal, I would write this as:

    Claims.CurrentDateTime in CURRENTDATE-1 to CURRENTDATE-366

    Any idea how to do this in Access? Thanks!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Comparable in Access would be

    Claims.CurrentDateTime Between Date()-1 And Date()-366
    Paul

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Assuming that's the exact business logic you want, then pbaldy is on point.

    However, I would recommend using the DateDiff() and DateAdd() functions for evaluating datetime fields as these functions will automatically account for oddities such as leap years.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, guys, i hate to break it to you but the earlier date has to go first
    Code:
    WHERE foo BETWEEN DATE()-365 AND DATE()-1
    this is due to the fact that BETWEEN is actually executed as a pair of inequalities
    Code:
    WHERE foo >= DATE()-365 
      AND foo <= DATE()-1
    the same logic applies to numbers, so for instance if you write this --
    Code:
    WHERE foo BETWEEN 8 AND 2
    this would be equivalent to
    Code:
    WHERE foo >= 8 
      AND foo <= 2
    and now it's perhaps a little easier to see why this will never return any rows

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would agree and always structure my own statements that way, but I was curious, having seen it work the other way. I tested Access queries against SQL Server linked tables and a local Access table. The queries against the linked tables behaved exactly as you describe, but queries against the local table didn't care. I tested both numeric and date fields. In either case, it didn't matter which value was first, they would return the correct values either way. Does Jet evaluate/execute the SQL differently based on the table?
    Paul

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pbaldy View Post
    Does Jet evaluate/execute the SQL differently based on the table?
    well whaddya know, i just tested it and i guess it does

    still, putting the higher BETWEEN value ahead of the lower one is a bad habit to get into, because i can say with certainty that it does ~not~ work in other databases
    Last edited by r937; 12-23-09 at 19:25.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I agree with you 100&#37;, I was just curious.
    Paul

Posting Permissions

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