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

Laura Sheldon
Laura Sheldon used Ask the Experts™
I have a query with expiration dates that I need to be able to insert a phrase in another field if that date is within a certain time frame. For example: Community A's Contract expires 7/31/12 so my query in the other field is 'if the expiration date is less than 12 months from today, then label here "Less Than 12"'. I have separate other fields for the other groups such as "Between 12 & 24", "Between 24 & 36", "Between 36 & 250" & Above 250".

Please let me know if more explanation is needed.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

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

Open in new window

For more about Switch, please see:
After you replace ExpirationDate with your field name,
use the following in a new field name of the query design view:

LessThan12: IIF(Month([ExpirationDate]-now()) < 12,"Less Than 12","")
Top Expert 2010

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

Open 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

Open 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
Top Expert 2010


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.



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


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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial