Link to home
Create AccountLog in
Avatar of Laura Sheldon
Laura SheldonFlag 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.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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
Avatar of Richard Daneke
Richard Daneke
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
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".

Avatar of Laura Sheldon

ASKER

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