Multiple IIf queries (1 Viewer)

steve1300

New member
Local time
Today, 09:58
Joined
Mar 24, 2017
Messages
7
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
 

Ranman256

Well-known member
Local time
Today, 12:58
Joined
Apr 9, 2015
Messages
4,339
If the IIF statements get too complicated ,the make a custom function.
Paste this code into a module.
In the query feed the fcn the days and compdate, usage:
GetStatus(dateDue-Date(), [compDate])

Code:
Function getStatus(pvDays,pvCompDate)
Select case true
Case pvDays<90 And IsNull(pvCompDate) 
      GetSatus="90 day warning "
Case pvDaysDate<0 And  IsNull(pvCompDate)
     GetSatus="Overdue"
Case else
      GetSatus="working"
End select
End function
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Feb 19, 2013
Messages
16,553
you are mixing datatypes (dates and text) in the same output. This can cause problems further down the line

Just to be clear

does 'If the date has passed ' mean the duedate? and what has it passed? Date? i.e. duedate>date?

Expr1: - is this the planned date?

does [completeddate] Is Null mean the 'assessment isn't done'?

Can you provide some example data and the outcome you are getting and the out come you want to get.
 

steve1300

New member
Local time
Today, 09:58
Joined
Mar 24, 2017
Messages
7
Thanks Ranman - I've never used this function before so am trying it out. I have tweaked it to add my field names in but unfortuntely the "Overdue" statement still doesn't show, although the other 2 you put in ("90 day warning" and "working") are fine, so it must be something I am showing wrong on my database, as both codes are giving the same fault.

Thanks for your help; using the module function is certainly easier

Steve
 

steve1300

New member
Local time
Today, 09:58
Joined
Mar 24, 2017
Messages
7
Thanks for your reply CJ, maybe that's the problem having text and date formats, so I will need to look at another way if this does cause issues.

Yep, the planner should show "Overdue" if the assessment date has not been inserted into the field. So if the assessment due date was yesterday and the assessment hadn't been done (date not entered into the [completeddate] field), the [duedate] field in the planner should show "overdue".

I've just checked, and if I remove just the first IIf statement, the formula works, and if I remove just the second IIF statement, it works, it just doesn't seem to do so with all 3 in there

Expr1 Is the final result of the 3 statements, so in the planner the field will show either the due date, Overdue, or will be blank - the datecompleted field will not be in here as the datasheet with this information on is just a visual reminder to let me see which of my staff are due/overdue assessments

Is Null means that no date has been entered into the datecompleted field.
Thanks
Steve
 
Last edited:

Ranman256

Well-known member
Local time
Today, 12:58
Joined
Apr 9, 2015
Messages
4,339
Then some other condition is not set.
You can pause the code with a breakpoint on the SELECT statement.
Then get the values by hovering the cursor over the variable.

I think completeDate may not be null.
Or
Use the correct date math value
DateDiff("d",[dueDate],date)
 

steve1300

New member
Local time
Today, 09:58
Joined
Mar 24, 2017
Messages
7
I've just checked Ranman, the [completeddate] field is definitely blank. As I mentioned above, the strange thing is, removing the first statement (so the formula does not put the planned date in the diary when the due date is within 90 days of the currrent date) makes the formula work ok - any assessments overdue show the statement "overdue" in the relevant field. Removing the second IIf statement (so it won't show "overdue") also has the formula working ok. As CJ suggested it may be mixing text and dates, so I changed the "Overdue" text and pointed the formula to another date field - didn't make any difference, the full formula wouldn't work. For some reason my database doesn't like more than 2 IIf formulas. Can't think of any more options so may have to lose an IIf statement.
Thanks again for your help
Steve
 

steve1300

New member
Local time
Today, 09:58
Joined
Mar 24, 2017
Messages
7
Guys

Worked it out! the problem was, the first 2 IIf statements were arguing with each other, as IIf statement 1 had criteria less than 90 days between 2 dates, and IIf 2's criteria was if the current date passes the planned date which, in effect means the solution is still less than 90 days, so the statements couldn't differentiate.

Solution is, I amended the first IIf statement to read IIf([duedate]-Date() Between 90 And 0 And [completeddate] Is Null,[duedate].

Thanks for your input both

Regards
Steve
 

Users who are viewing this thread

Top Bottom