• Status: Solved
• Priority: Medium
• Security: Public
• Views: 256

# Comparing Dates that fall within a range

Hello Experts,

I have a date and I would like to know if it falls within the follow ranges in a query:

-Occurs within next 10 Days
-Ocurrs within next 11 thru 30 Days
-Occurs within next 31 thru 60 Days
-Occurs within next 61 thru 90 Days
-Occurs within 90+ Days

How do I accomplish this within an expression?

Thank you

0
• 3
• 2
• 2
1 Solution

Commented:
Try this:

``````SELECT Date() AS Today, MyDate,
Switch(DateDiff("d", Date(), MyDate) <= 10, "Next 10",
DateDiff("d", Date(), MyDate) <= 30, "11 - 30",
DateDiff("d", Date(), MyDate) <= 60, "31 - 60",
DateDiff("d", Date(), MyDate) <= 90, "61 - 90",
True, "90+") AS Category
FROM SomeTable
WHERE MyDate >= Date()
``````

For more about the Switch function, please see:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
0

Commented:
So your date is in a field (we will call it SuspenseDate since you didn't provide any specifices)?

If you want the query to output 5 separate columns that indicate whether the record falls within one of those date spans then you could do something like:

SELECT SuspenseDate,
iif(DateDiff("d", date(), [SuspenseDate]) Between 0 and 10, True, False) as [Next 10],
iif(DateDiff("d", date(), [SuspenseDate]) Between 11 and 30, True, False) as [11 to 30],
iif(DateDiff("d", date(), [SuspenseDate]) Between 31and 60, True, False) as [31 to 60],
iif(DateDiff("d", date(), [SuspenseDate]) Between 61 and 90, True, False) as [61 to 90],
iif(DateDiff("d", date(), [SuspenseDate]) >90, True, False) as [greater than 90]
FROM yourTable

0

Commented:
On the other hand, if you want to know which group each record falls in, I prefer to create another table (tbl_Ranges) which contains fields GT (greater than or equal), LT (Less than), RangeName that would look like:

GTE    LT       RangeName
0        11       Next 10
11       31      11 to 30
31       60      31 to 60
61       90      61 to 90
90     NULL    Greater than 90

Then you would create a query that look like:

SELECT SuspenseDate, RangeName
FROM yourTable, tbl_Ranges
WHERE yourTable.SuspenseDate >= tbl_Ranges.GTE
AND yourTable.SuspenseDate < tbl_Ranges.LT
0

Author Commented:
Savant,

I tried this but cannot seem to get the syntax right.

SELECT Import_T.[Key Field], Import_T.[Booking Region], Import_T.[Forecast Ind#], Import_T.[Account Name], Import_T.[Account Manager], Import_T.[Opportunity Name], Import_T.[Crystal ID], Import_T.[Pipeline Stg#], Import_T.[Proposal Status], Import_T.[Estimator Name], Import_T.[Proposal No#], Import_T.[Proposal Rev No], Import_T.[Estimated Book Date], Import_T.[Product Category], Import_T.[Product Detail], Import_T.[Product Estimated Sell Amt], Import_T.HwlMarkUp, Import_T.EstCost, Import_T.ThirdPartyPercent, Import_T.Est3rdPartyBudget, Import_T.CostReductionPercent, Import_T.EstCostReduction, Import_T.ActuaThirdPartyBudget, Import_T.ActualThirdPartyPercent, Import_T.ProjectedCostReduction, Import_T.BuyerName, IIf(IsNull([Import_T]![BuyerName]),"No","Yes") AS BuyerAssigned, IIf([Product Estimated Sell Amount Not Null]<500000,"<500K",IIf([Product Estimated Sell Amount Not Null]>=500000,"=500K")) AS [Product Est Sell Amount], IIf(IsNull([Product Estimated Sell Amt]),0,[Product Estimated Sell Amt]) AS [Product Estimated Sell Amount Not Null], IIf(IsNull([Import_T]![Probability Win Pct#]),"<50%",IIf([Import_T]![Probability Win Pct#]<0.5,"<50%",IIf([Import_T]![Probability Win Pct#]>=0.5,"=50%"))) AS [Probability Win Pct Groups], [Import_T]![Estimated Book Date], Switch(DateDiff("d", Date(),[Import_T]![Estimated Book Date]) <= 10, "Next 10",DateDiff("d", Date(),[Import_T]![Estimated Book Date]) <= 30, "11 - 30", DateDiff("d", Date(),[Import_T]![Estimated Book Date]) <= 60, "31 - 60",DateDiff("d", Date(),[Import_T]![Estimated Book Date]) <= 90, "61 - 90", True, "90+) AS Category FROM Import_T  WHERE [Import_T]![Estimated Book Date]>= Date()
FROM Import_T
ORDER BY IIf(IsNull([Import_T]![Probability Win Pct#]),"<50%",IIf([Import_T]![Probability Win Pct#]<0.5,"<50%",IIf([Import_T]![Probability Win Pct#]>=0.5,"=50%"))), Import_T.[Probability Win Pct#];
0

Commented:
You're missing a doublequote at the end of the 90+ label.

So, this:

"90+

should be:

"90+"
0

Author Commented:
Fyed,

I goit your solution to work; but I was looking this to be display is a single column within my exist query, which looks like this:

SELECT Import_T.[Key Field], Import_T.[Booking Region], Import_T.[Forecast Ind#], Import_T.[Account Name], Import_T.[Account Manager], Import_T.[Opportunity Name], Import_T.[Crystal ID], Import_T.[Pipeline Stg#], Import_T.[Proposal Status], Import_T.[Estimator Name], Import_T.[Proposal No#], Import_T.[Proposal Rev No], Import_T.[Estimated Book Date], Import_T.[Product Category], Import_T.[Product Detail], Import_T.[Product Estimated Sell Amt], Import_T.HwlMarkUp, Import_T.EstCost, Import_T.ThirdPartyPercent, Import_T.Est3rdPartyBudget, Import_T.CostReductionPercent, Import_T.EstCostReduction, Import_T.ActuaThirdPartyBudget, Import_T.ActualThirdPartyPercent, Import_T.ProjectedCostReduction, Import_T.BuyerName, IIf(IsNull([Import_T]![BuyerName]),"No","Yes") AS BuyerAssigned, IIf([Product Estimated Sell Amount Not Null]<500000,"<500K",IIf([Product Estimated Sell Amount Not Null]>=500000,"=500K")) AS [Product Est Sell Amount], IIf(IsNull([Product Estimated Sell Amt]),0,[Product Estimated Sell Amt]) AS [Product Estimated Sell Amount Not Null], IIf(IsNull([Import_T]![Probability Win Pct#]),"<50%",IIf([Import_T]![Probability Win Pct#]<0.5,"<50%",IIf([Import_T]![Probability Win Pct#]>=0.5,"=50%"))) AS [Probability Win Pct Groups], Import_T.[Probability Win Pct#], IIf([Import_T]![Estimated Book Date]<Date(),"Occured In Past",IIf([Import_T]![Estimated Book Date]=Date(),"Occurs Today")) AS [Estimated Book Date Group]
FROM Import_T
ORDER BY IIf(IsNull([Import_T]![Probability Win Pct#]),"<50%",IIf([Import_T]![Probability Win Pct#]<0.5,"<50%",IIf([Import_T]![Probability Win Pct#]>=0.5,"=50%"))), Import_T.[Probability Win Pct#];
0

Author Commented:
That's the ticket!  Thank you
0

## Featured Post

• 3
• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.