Sometimes, complex expressions are handled better by functions that your query can call. e.g.
Code:
Function Vacations(dteStart As Variant, dteEnd As Variant) As String
Select Case Date
Case dteStart To dteEnd
Vacations = "On Vacation"
Case Is >= dteEnd
Vacations = "Already Applied"
Case (IsNull(dteStart) And IsNull(dteEnd))
Vacations = "Available"
End Select
End Function
You include the date fields in your query and have a calculated field like Status: Vacations([AvStart],[AvEnd]) - using your own table and field names of course. Perhaps you'd agree that this would certainly be a better way if you had many possibilities, rather than having a mind-bending set of nested IIF's.
Not saying this would be better in your case, just putting it out for consideration.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.