Nested IIF statement problem

As you have seen, nesting IIF(0 is prone to breaking and hard to maintain (we can't figure it out so how will your successor)

Try to build a Select Case True structure instead

Select Case True
   Case IsNull([dbo_vw_All_Employee_List]![TermDate])
        'whatever
   Case dbo_vw_All_Employee_List]![DOH]<[TempVars]![DateFrom]
        'whatever
   Case [dbo_vw_All_Employee_List]![TermDate]<[TempVars]![DateFrom]
        'whatever
   Case [dbo_vw_All_Employee_List]![TermDate]>=[TempVars]![DateTo]
        'whatever
    Case else
         'something to cover possibilities you haven't foreseen
End Select

Order them by the most specific case first, most general case last
The first case to evaluate as true happens, and the rest don't execute
This way you separate the tests (Cases) from the effects ( indented whatevers)
Nested IIFs mix it all up and are hard to read and decipher
Don't use them!

I agree about using a Select Case statement instead of a complex nested Iif statement.  Some other suggestions:

Name your controls with the appropriate prefixes (txt, cbo, etc.) to avoid confusion with their bound fields.

Save the values from controls to variables of the appropriate data types

Use Debug.Print statements to examine the variables.  

Here is an example of setting a Date variable from a value in a control (with testing):

   If IsDate(Me![txtToDate].Value) = True Then
      dteToDate = CDate(Me![txtToDate].Value)
   End If

Select allOpen in new window

Name your controls with the appropriate prefixes (txt, cbo, etc.) to avoid confusion with their bound fields.
I've never understood that advice.
The default in A2003+ is for controls to take the name of their bound field.
The controls collection is the default property of forms and reports
There's no Intellisense support for the ! operator

And you can't refer to fields with Me!SomeBoundField anyway
The only way you can get the value of the field is through a bound control.

So what is the origin of that advice?

Nick67

Breaking down the iif statements and testing helped me find the error:

A missing ")".

Thanks

Glen

Nick -- in some cases, there can be errors because certain methods and properties apply to fields but not controls (or vice versa).  This is rare, but it can happen.  The ! operator is correct for collections.  In some cases the . operator will work (though that was more the case in earlier versions of Access), but I like to use it to make code clearer.  For some other dialects of VB, there is no ! operator, but Access makes the distinction, so we might as well use the correct operator.

As for getting the value of a field, that does not require going through a control.  The first line below gets the value of the ID field directly from the field (on a bound form); the second gets it through a control.  Since the record might not be saved yet, these lines can yield different results.

lngID = Me![ID]
lngID = Nz(Me![txtID].value)

Select allOpen in new window