Tracking expiration dates 90 days, 30 days, expired and emailing each advisory

[samramirezjr]

I need a database for work that tracks expiration dates of DOT Physicals and Drivers License expiration dates. I need it to alert management when thresholds are reached. 90 days, 30 days, expired... Seems like it would be easy, but I have know clue how to accomplish this mission. I have attached what I have so far (Not a lot) any help would be greatly appreciated.

[ranman256]

In a query Q1, calc the days to expire: Days2Exp: DateDiff("d",[expDate],date())
In Q2, use Q1 using either an IIF statement or the funtion below to get the text result.

public Function calcExpStat(byval  pvDays)

Select case pvDays
   Case < 1
          calcExpStat = "expired"
   Case < 60
           calcExpStat = "60days"
   Case < 90
          calcExpStat = "90days"
   Case else
         calcExpStat = ""
end function

[CJ_London]

or use the choose function in a query (similar to a case statement)

choose(DateDiff("d",[expDate],date())<1,"Expired",DateDiff("d",[expDate],date())<60,"60 Days",DateDiff("d",[expDate],date())<90,"90 Days")