Hi everyone
I appreciate this type of problem has been covered many times, but am trying a query that I have not yet come across here.
I have an assessment planner for my staff that predicts due assessments. The dates are predicted in a query, and are displayed in a form when the due date is less than 90 days from the current date. When an assessment is complete the date is entered into a field in a table; once this date goes in, the due date should disappear. If the assessment is not done by the due date, the planner should say "Overdue".
This is what I've tried:
Expr1:IIf([duedate]-Now()<90 And [completeddate] Is Null,[duedate],IIf([duedate]<Now() And [completeddate] Is Null,"Overdue",IIf([duedate] Is Not Null,"")))
I have used Date() in place of Now() and have tried Switch as below
Expr1: Switch([duedate]-Date()<90 And [completeddate] Is Null,[duedate],[duedate]<Date() And [completeddate] Is Null,"Overdue",[duedate] Is Not Null,"")
Everything works except the 'Overdue' part of it. If the date has passed and the assessment isn't done, the planned date that is now overdue is still displayed
Thanks for any help
Steve
I appreciate this type of problem has been covered many times, but am trying a query that I have not yet come across here.
I have an assessment planner for my staff that predicts due assessments. The dates are predicted in a query, and are displayed in a form when the due date is less than 90 days from the current date. When an assessment is complete the date is entered into a field in a table; once this date goes in, the due date should disappear. If the assessment is not done by the due date, the planner should say "Overdue".
This is what I've tried:
Expr1:IIf([duedate]-Now()<90 And [completeddate] Is Null,[duedate],IIf([duedate]<Now() And [completeddate] Is Null,"Overdue",IIf([duedate] Is Not Null,"")))
I have used Date() in place of Now() and have tried Switch as below
Expr1: Switch([duedate]-Date()<90 And [completeddate] Is Null,[duedate],[duedate]<Date() And [completeddate] Is Null,"Overdue",[duedate] Is Not Null,"")
Everything works except the 'Overdue' part of it. If the date has passed and the assessment isn't done, the planned date that is now overdue is still displayed
Thanks for any help
Steve