# Thread: A better way to compare a date range to a date range

1. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
check this --

Absences between a date range... - dBforums

starting at post #14

3. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
jerry, did you see the diagram in the other thread i linked to?

i believe your code handles only case #3

5. Registered User
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
•