Avatar of Laura Sheldon
Laura Sheldon
Flag for United States of America asked on

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

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.
Microsoft AccessSQL

Avatar of undefined
Last Comment
Laura Sheldon

8/22/2022 - Mon
Patrick Matthews

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:

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

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

Richard Daneke

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Patrick Matthews

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
GRayL

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".

Laura Sheldon

ASKER
Thanks! I always forget where to put the "Day", "Month", "Year"! Appreciate it!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.