Access 2007, inserting a phrase for a field that is defined by a date in a query

If those are the only ranges you will ever need...

SELECT Customer, ExpiryDate, Switch(DateDiff("m", Date(), ExpiryDate) < 12, "< 12", DateDiff("m", Date(), ExpiryDate) < 24, "12 - 24", DateDiff("m", Date(), ExpiryDate) < 36, "24 - 36", DateDiff("m", Date(), ExpiryDate) < 250, "36 - 250", True, ">= 250")
FROM SomeTable

Select allOpen in new window

For more about Switch, please see:

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

If your ranges are subject to change, I would make this table-based.

1) Create a new table, notionally named tblExpiryRanges, like this:

StartRange       EndRange         Label
0                      12                    < 12
12                     24                    12 - 24
24                     36                    24 - 36
36                     250                   36 - 250
250                   1000000             >= 250

Select allOpen in new window

Now your query becomes:

SELECT t1.Customer, t1.ExpiryDate, t2.Label
FROM SomeTable t1, tblExpiryRanges t2
WHERE DateDiff("m", Date(), t1.ExpiryDate) >= t2.StartRange And DateDiff("m", Date(), t1.ExpiryDate) < t2.EndRange

Select allOpen in new window

Oops, subtracting two dates leaves you with the number of days difference, so correct this to:

LessThan12: IIf(([ExpirationDate]-Now())<365,"Less Than 12","") in Qery Design or

IIf(([ActivityDate]-Now())<365,"Less Than 12","") as LessThan12  ins SQL

lmlsRPhouse,

Note that usinh DateDiff might not give you quite what you want.

For example, suppose that the ExpiryDate is 2012-08-01.  DateDiff("m", Date(), ExpiryDate) will give 12, even though depending on your business rules you might say the answer should be 11, because it will be fewer than 12 full months.

This is because DateDiff, in Access, VBA, and in SQL Server does **not** calculate the true elapsed time between two dates.  Rather, it returns the number of time period boundaries between two dates.  Thus, for

      DateDiff("yyyy", Date1, Date2)   [use DATEDIFF(year, Date1, Date2) for SQL Server]

the function is looking for the number of **year boundaries** between the two dates and **not** the number of full years that have passed.  If Date1 and Date2 are 01/08/1924 and 01/07/2007, then 83 year boundaries are counted.

Similarly, if Date1 = 2009-12-31 23:59:59 and Date2 = 2010-01-01 00:00:00, even though only one second elapsed, the function would *still* return a 1, because you have that year boundary.

If we need to take this into account, please let me know.

Patrick

Shouldn't:

other groups such as "Between 12 & 24", "Between 24 & 36", "Between 36 & 250" & Above 250".

be:

other groups such as "Between 13 & 24", "Between 25 & 36", "Between 37 & 250" & Above 250".

Thanks! I always forget where to put the "Day", "Month", "Year"! Appreciate it!