Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    71

    Unanswered: A better way to compare a date range to a date range

    This is not an actually problem but wondering if there is a more effecient way of comparing date ranges or ranges in general. Currently the only way I'm aware of comparing 2 date ranges is a 4 nested if statement.

    Example:
    a and b 1st date range
    x and y 2nd date range

    IIF(a Between x AND y , 1,
    IIf(b Between x AND y, 1,
    IIf(x Between a AND b,1 ,
    IIf(y Between a AND b,1,0))))
    So thats as an equation, but same thing can be done in VBA or where ever might be needed. I've searched google and this site and haven't found anything that can help me simplify it. Thanks for your time.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    check this --

    Absences between a date range... - dBforums

    starting at post #14

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

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    If you are trying to find if dates in the range A and B fall within the date range X and Y, without using several nested IFFs, here's my attempt at this, which I tested in a query:

    Code:
     IIf([DateA]>=[DateX] And [DateB]<=[DateY],"In Range","Not In Range")
    Jerry

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jerry, did you see the diagram in the other thread i linked to?

    i believe your code handles only case #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2011
    Posts
    71
    r937 thanks for that and the diagram. I had drawn 100 things on my blackboard but not one quite so simple. Appreciate the info. Can't believe the answer was so simple, ha.

Posting Permissions

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