I currently have a status query in my database to automatically show an individuals status "Current", "Suspended", "Expired" based on the date of their last exam.
I'm trying to get a fourth status to show, but haven't had any luck with the code. I am using MS Access 2013.
I currently show "CURRENT" for those whose date is less than 180 days, "SUSPENDED" if their date is between 181 and 365 days and Expired if over 365 days.
What I am trying to do is show Expired for dates between 365 and 730 days and add an "INACTIVE" status for those 731 days or greater... I haven't had much luck... below is what I am using right now and it works great, but trying to expand the status.
Status: IIf(DateDiff("d",[Cert_CurrencyDate],Date())<=180,"CURRENT",IIf(DateDiff("d",[Cert_CurrencyDate],Date())>=181 And DateDiff("d",[Cert_CurrencyDate],Date())<365,"SUSPENDED","EXPIRED"))
Thanks in advance for any help.
Nathan
I'm trying to get a fourth status to show, but haven't had any luck with the code. I am using MS Access 2013.
I currently show "CURRENT" for those whose date is less than 180 days, "SUSPENDED" if their date is between 181 and 365 days and Expired if over 365 days.
What I am trying to do is show Expired for dates between 365 and 730 days and add an "INACTIVE" status for those 731 days or greater... I haven't had much luck... below is what I am using right now and it works great, but trying to expand the status.
Status: IIf(DateDiff("d",[Cert_CurrencyDate],Date())<=180,"CURRENT",IIf(DateDiff("d",[Cert_CurrencyDate],Date())>=181 And DateDiff("d",[Cert_CurrencyDate],Date())<365,"SUSPENDED","EXPIRED"))
Thanks in advance for any help.
Nathan
Last edited: