Solved Multiple If Expression (1 Viewer)

[Teri Bridges]

Hi folks, I am trying to write a multiple-expression but do not know how to connect them properly. Help is appreciated.

=IIf(not IsNull( [CompleteDate] , "Complete", " ", IIf(IsNull( [ReviewDate] , "Not Started)," ", IIF( [DueDate] > Day(), "Inprocess","OverDue")))

 

[Ranman256]

rather that making messy nested IIFs , just make a custom function that reads cleaner:

usage:
=getResult( [CompleteDate], [ReviewDate], [DueDate])

Public Function getResult(pvCompDte, pvRevDte, pvDueDte)
Select Case True
   Case Not IsNull(pvCompDte)
       getResult = "Complete"
       
   Case IsNull(pvRevDte)
       getResult = "Not Started"
   
   Case pvDueDte > Date
       getResult = "Inprocess"
   
   Case Else
       getResult = "OverDue"
End Select
End Function

 

[Mike Krailo]

You had missing parentheses on your IsNull() functions and one parentheses that should have been a double quote.

=IIf(not IsNull( [CompleteDate] , "Complete", " ", IIf(IsNull( [ReviewDate] , "Not Started)," ", IIF( [DueDate] > Day(), "Inprocess","OverDue")))

Should Be:

=IIf(not IsNull( [CompleteDate] ), "Complete", " ", IIf(IsNull( [ReviewDate]) , "Not Started"," ", IIF( [DueDate] > Day(), "Inprocess","OverDue")))

I totally agree with Ranman256 on using his suggested function as it is so much easier to read.

 

[Teri Bridges]

You had missing parentheses on your IsNull() functions and one parentheses that should have been a double quote.


Should Be:

=IIf(not IsNull( [CompleteDate] ), "Complete", " ", IIf(IsNull( [ReviewDate]) , "Not Started"," ", IIF( [DueDate] > Day(), "Inprocess","OverDue")))

I totally agree with Ranman256 on using his suggested function as it is so much easier to read.

If I try this I get the wrong Number of arguments

 

[Teri Bridges]

rather that making messy nested IIFs , just make a custom function that reads cleaner:

usage:
=getResult( [CompleteDate], [ReviewDate], [DueDate])

Public Function getResult(pvCompDte, pvRevDte, pvDueDte)
Select Case True
   Case Not IsNull(pvCompDte)
       getResult = "Complete"
      
   Case IsNull(pvRevDte)
       getResult = "Not Started"
  
   Case pvDueDte > Date
       getResult = "Inprocess"
  
   Case Else
       getResult = "OverDue"
End Select
End Function

If I try this I I get

 

[Teri Bridges]

If I try this I I get
View attachment 110146

I have a glitch. The in-process date shows as Overdue. the Complete, Overdue, and Not Started statuses are working. The in in-process status is not working. It should show In process if the due date is greater than today.

Public Function getResult(CompleteDate, ReviewDate, DueDate)
Select Case True
Case Not IsNull(CompleteDate)
getResult = "Complete"

Case IsNull(ReviewDate)
getResult = "Not Started"

Case DueDate > Date
getResult = "OverDue"

Case Else
getResult = "In Process"
End Select
End Function

 

[plog]

Read and understand Ranman's post, don't just skim and copy/paste half of it.

 

[Teri Bridges]

I have a glitch. The in-process date shows as Overdue. the Complete, Overdue, and Not Started statuses are working. The in in-process status is not working. It should show In process if the due date is greater than today.

Public Function getResult(CompleteDate, ReviewDate, DueDate)
Select Case True
Case Not IsNull(CompleteDate)
getResult = "Complete"

Case IsNull(ReviewDate)
getResult = "Not Started"

Case DueDate > Date
getResult = "OverDue"

Case Else
getResult = "In Process"
End Select
End Function
View attachment 110147

I Tried to add a line

Public Function getResult(CompleteDate, ReviewDate, DueDate)
Select Case True
Case Not IsNull(CompleteDate)
getResult = "Complete"

Case IsNull(ReviewDate)
getResult = "Not Started"

Case DueDate > Date
getResult = "OverDue"

Case DueDate < Date
getResult = "In Process"

Case Else
getResult = " "
End Select
End Function

 

[Mike Krailo]

I don't know what the Day() function is, does that have arguments?

 

[Teri Bridges]

I am sorry guys. I am having a hard time. I can not figure out how to write simple Expressions. I have read and watched videos on If statements and if I understand correctly there is supposed to be an expression, a True, and a false. I Wrote this but get too many arguments
=IIf(IsNull([CompleteDate], "","Complete")) Any suggestions on a sight where I could learn more?

 

[Teri Bridges]

I don't know what the Day() function is, does that have arguments?

I thought Day() called today's date
=IIf(not IsNull( [CompleteDate] ), "Complete", " ", IIf(IsNull( [ReviewDate]) , "Not Started"," ", IIF( [DueDate] > Day(), "Inprocess","OverDue")))

 

[Gasman]

That would be Date()
What was wrong with the function offered?

 

[Josef P.]

=Switch(CompleteDate IS NOT NULL, "Complete", ReviewDate Is Null, "Not Started", DueDate > Date(), "Inprocess", DueDate <= Date(), "OverDue", True, "???")

 

[Mike Krailo]

Those darn ternary iif's can be a pain.

 

[Gasman]

Surely the False option is just another Iif until the last one?

 

[Teri Bridges]

=Switch(CompleteDate IS NOT NULL, "Complete", ReviewDate Is Null, "Not Started", DueDate > Date(), "Inprocess", DueDate <= Date(), "OverDue", True, "???")

I cannot get it to work correctly. The overdue date does not come in.

 

[Teri Bridges]

Those darn ternary iif's can be a pain.

Indeed!!

 

[Josef P.]

Without words ...

 

[Teri Bridges]

That would be Date()
What was wrong with the function offered?

I cannot Get the overdue to show correctly

Without words ...
View attachment 110156

Sorry I should have stated that the forms are not Done.

I am working with the Query 1 Query and the Query 1 Form. My bad.
When I get the statuses working correctly I was going to clean up. This is an excerpt of my read DB where I play to learn.

 

[Mike Krailo]

I'm still trying to figure out why Ranman's function didn't work for you. You said you got a prompt for parameter getResult, but that doesn't make any sense. That function returns a string based on your three parameters. He gave you the exact usage.

I see from post #5 that you have a field in the table called Status, yet you are now trying to calculate the status in the query. If you are calculating the field in the query, you should not have a bound field called status. You cannot have a bound field called status and then try to alias it to status the way you did in the provided picture.