nested IIF

[jkncrew]

My first post ever... so here goes.

My Goal:

Columns of data contain date fields & "Status" that indicates where a case lies. I'm trying to calculate days spent in a specific area (determined by status code) and total amount of time spent there all together. Status code begining w/ "I***" means case was sent to an area, w/ "R***" it returned. I have it together up to a point. My problem lies with end of expression where I want to calculate last date at specific location to now. In line 1 it would be from 5/03/07 to now since no r162 follows & blank field follows. Problem is with second IIF - it acts independently instead of giving false value from first IIF... i think.

status1 date1 status2 date2 status3 date3 status4 date4
1) I162 5/3/07 I004 5/20/07 I006 6/1/07
2) I162 4/2/07 R162 4/30/07

My failure:

stat2n4: IIf([status2]="i162" Or [status2]='i062' Or [status2]='i009' Or [status2]='i159' And [status3]<>'r162' Or [status3]<>'r162' And [Status4]="r162",DateDiff("d",[date2],[date4]),IIf(IsNull([status4]),DateDiff("d",[date4],Now())))

 

[pbaldy]

2 things I notice right off. First, I suspect you need some parenthesis in the first test. With all the and's & or's, Access is almost guaranteed to misinterpret how you want them grouped. Second, there is no false argument in the second IIf().

And I guess I should mention that your design does not look normalized, due to the repeating fields.

 

[KeithG]

I agree you should normalize you data. But if not, you should check out the Switch function.

 

[Moniker]

Put this function in a module.

Function GetStatus(s2 As String, s3 As String, s4 As String, d2 As Date, d4 As Date) As Date

    Select Case s2
	Case "i162","i062","i009","i159"
            If s3 <> "r162" And s4 = "r162" Then
                GetStatus = DateDiff("d",d2,d4)
            End If
        Case Else
	    GetStatus = Switch(Nz(d4,"")="", DateDiff("d",d4,Now()), True, "No Status")
    End Select

End Function

To call it from your query, you'd do this:

stat2n4: GetStatus([status2],[status3],[status4],[date2],[date4])

 

[jkncrew]

Thank you all for responding.

To PBaldy & KeithG - not sure what you meant by "normalize your data", but I suspect you believe the data is not well-structured. If so, I agree - trouble is that my database is linked to a flat file that the mainframe spits out and I have no control over that aspect.

I tried entering the "false" for the 2nd IIF, but when I do, results still not right. Would have liked seeing an example of how to group using parentheses - could not find material on how to do that. Please allow me so say what I want in non-technical terms:

- (first IIF) If Status2= (I162 or I062 or I009 or I159) AND Status3 IS NOT (I162 or R162) AND Status 4= R162 then give date range from Date2 to Date4

- (second IIF) if all conditions from first IIF exist and Status4 IS NULL, give date range from date2 to now.

My last changes: (2 typos in original)

stat2n4: IIf([status2]="i162" Or [status2]="i062" Or [status2]="i009" Or [status2]="i159" And [status3]<>"I162" Or [status3]<>"r162" And [Status4]="r162",DateDiff("d",[date2],[date4]),IIf(IsNull([status4]),DateDiff("d",[date2],Now()),0))

To Moniker:

I have never worked with modules, however I tried creating one by just pasting what you gave me but I get “data type mismatch” error – I tried modifying as shown below, in case I needed to enter the actual field names, but I get same error.

Function GetStatus(status2 As String, status3 As String, status4 As String, date2 As Date, date4 As Date) As Date

Select Case stat2n4
Case "i162", "i062", "i009", "i159"
If status3 <> "r162" And status4 = "r162" Then
GetStatus = DateDiff("d", date2, date4)
End If
Case Else
GetStatus = Switch(Nz(status4, "") = "", DateDiff("d", date2, Now()), True, "No Status")
End Select

End Function

 

[Moniker]

You're getting a data type mismatch because you're trying to run the select case on stat2n4, and the function has no idea what that is. This is wrong: "Select Case stat2n4". This is right: "Select Case Status2". That's after you've reworded my variable names.

For a simple and quick explanation of what's going on, know these things:

1) Unlike a subroutine, a function will return a value of some sort.
2) You get that value by setting the name of the function to the value you want.
3) You pass the function the variables needed to calculate and/or determine its outcome value.

Therefore, where your original giant IIF statement was "stat2n4: Giant IIF statement here", the functional equivalent is "stat2n4: GetStatus(variables here)".

Here's a very simple explanation of what's going on. Make this function:

Function AddTogether(FirstNum As Integer, SecondNum As Integer) As Integer

AddTogether = FirstNum + Second Num

End Function

After the name of the function, you define the variables that you will pass to it along with their data types (FirstNum and SecondNum are both integers in this case). The last part of the function declaration is the data type to be returned, again in this case, an integer. To call this function, you would say something like this:

MyResult = AddTogether(1,3)

After running that, "Myresult" would equal 4.

That is clearly a very simplified version of a function as it's easier to just add numbers together, but I am trying to help you understand how functions work in the first place.

They happen to be especially useful in cases like yours where you're testing the values of several variables to set the value of one other variable. They help you avoid overly obtuse, confusing, and hard to maintain SQL structures like multiple nested IIF statements.

As someone mentioned earlier, the SWITCH function is also very useful in situations where you only need one result. While some don't think there's a "false" result for a SWITCH function, there is. You test for all the true possibilities first, and then force a true value at the end for a false. SWITCH works like this:

VariableName = Switch(Condition1, ValueIfCondition1IsTrue, Condition2, ValueIfCondition2IsTrue, ...)

The way to set a false is to end a switch with something that's always a true value; namely, the word True, which is a keyword in Access. Let's say you want to check to see if the value of a number is even. This will tell you even or odd based on the integer passed:

EvenOdd = Switch(YourInteger Mod 2 = 0, "Even", True, "Odd")

The equivalent If/Then variant of that is:

If YourInteger Mod 2 = 0 Then
    EvenOdd = "Even"
Else
    EvenOdd = "Odd"
End If

Tell me which is the cleaner code.

HTH

 

[jkncrew]

Nested IIF

To Moniker:

Moniker - Thanks for your in-depth reply. I couldn't figure out what keyword connected the module to the query and vise versa. Based on your response, "GetStatus" is the connection I was looking for and my attempt to force any other connection between the two was just futile. I was expecting the query to have a statement that said "run module1" or perhaps the module itself would need the word "status2n4" to link to the query - or something like that. It blew me away that the query would automatically reference the module (and there could have been many modules) with just the key word "GetStatus". Anyway, all this is probably just a lot of kindergarten blabber to you

Having said all this... I made the correction, but I still get the "data type mismatch.." error. I copied what I have in the module and pasted below. I wondered if at the end of the first line, the word "date" should be "integer" since my results from DateDiff would be a number of days drawn from a date range. Or perhaps the false statement "No Status" at the very bottom should be zero since the field cannot accommodate both integer and string. I'm fishing, and I must admit that I tried it both ways and the error is still the same. Any ideas?

Function getstatus(status2 As String, status3 As String, status4 As String, date2 As Date, date4 As Date) As Date

Select Case status2
Case "i162", "i062", "i009", "i159"
If status3 <> "r162" And status4 = "r162" Then
getstatus = DateDiff("d", date2, date4)
End If
Case Else
getstatus = Switch(Nz(date4, "") = "", DateDiff("d", date2, Now()), True, "No Status")
End Select

End Function

 

[Moniker]

Change it to:

Function getstatus(status2 As String, status3 As String, status4 As String, date2 As Date, date4 As Date) As Variant

If you're still getting the error, let me know.

 

[jkncrew]

Nested IIF

Moniker:

Changing to "Variant" cleared the "Datatype mismatch" error, but now the query results are either "No Status" or "#Error". The language may need some tweaking, but there seems to be somewhat of a pattern as to where the "no status" & #errors are falling, and it's looking like the query is trying to either meet condition1 and if not true, a new condition2 applies. As I explained to PBaldy & KeithG in my initial reply, condition2 should still meet the same criteria as condition1 with the exception of status4 (the only variation) as explained here:

- (first IIF) If Status2= (I162 or I062 or I009 or I159) AND Status3 IS NOT (I162 or R162) AND Status 4= R162 then give date range from Date2 to Date4

- (second IIF) if all conditions from first IIF exist and Status4 IS NULL, give date range from date2 to now.

The IIf monster I created never got anywhere and I think Igor is hitting on my wife...

Did I say that?

Don't mind me, it's late (12:48) ..need to go to bed

 

[jkncrew]

PBaldy:

Thanks - The grouping with () was most helpful.

Moniker: Thanks for introducing me to modules and giving me insight on how functions work.

If anyone was following this - the expression worked as follows:

Stat2n4: IIf(([status2]='I162' Or [status2]='I062' Or [status2]='I009' Or [status2]='I159') And ([status3] Like 'i???') And ([status4]='r162' Or [status4]='r062' Or [status4]='r159'),DateDiff("d",[date2],[date4]),IIf(IsNull([status4]) And ([status2]='I162' Or [status2]='I062' Or [status2]='I009' Or [status2]='I159') And ([status3] Like 'i???'),DateDiff("d",[date2],Now()),0))

...a bit long, but impressive to my peers - they think I'm a genius