Nested IIF statement problem

I am having a problem determining where the following nested IIF clause has a syntax error:

IIf(IsNull([dbo_vw_All_Employee_List]![TermDate]),
iif(
dbo_vw_All_Employee_List]![DOH]<[TempVars]![DateFrom],
8,
DateDiff("m",dbo_vw_All_Employee_List]![DOH],[TempVars]![DateTo])+1
),
IIf(
[dbo_vw_All_Employee_List]![TermDate]<[TempVars]![DateFrom],
0,
IIf(
[dbo_vw_All_Employee_List]![TermDate]>=[TempVars]![DateTo],
DateDiff("m",[TempVars]![DateFrom],[TempVars]![DateTo])+1,
DateDiff("m",[TempVars]![DateFrom],[dbo_vw_All_Employee_List]![TermDate])
)
)
)

Open in new window


I would appreciate any help finding the error.

Thanks

glen
GPSPOWAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
IIf(
   IsNull([dbo_vw_All_Employee_List]![TermDate]),
   iif(
      dbo_vw_All_Employee_List]![DOH]<[TempVars]![DateFrom],
      8,
      DateDiff("m",dbo_vw_All_Employee_List]![DOH],[TempVars]![DateTo])+1
   ),
   IIf(
      [dbo_vw_All_Employee_List]![TermDate]<[TempVars]![DateFrom],
      0,
      IIf(
         [dbo_vw_All_Employee_List]![TermDate]>=[TempVars]![DateTo],
         DateDiff("m",[TempVars]![DateFrom],[TempVars]![DateTo])+1,
         DateDiff("m",[TempVars]![DateFrom],[dbo_vw_All_Employee_List]![TermDate])
      )
   )
)

Open in new window


Hmmm...you didn't give us much to go on since we don't have your DB.

I'd suggest breaking each IIF into a separate control (termporarily) and see which one gives you an error.  That should help narrow it down.

e.g. create a new text box and add DataSource =IsNull([dbo_vw_All_Employee_List]![TermDate])
and another that has =iif(
      dbo_vw_All_Employee_List]![DOH]<[TempVars]![DateFrom],
      8,
      DateDiff("m",dbo_vw_All_Employee_List]![DOH],[TempVars]![DateTo])+1
   )
and another with =IIf(
      [dbo_vw_All_Employee_List]![TermDate]<[TempVars]![DateFrom],
      0,
      IIf(
         [dbo_vw_All_Employee_List]![TermDate]>=[TempVars]![DateTo],
         DateDiff("m",[TempVars]![DateFrom],[TempVars]![DateTo])+1,
         DateDiff("m",[TempVars]![DateFrom],[dbo_vw_All_Employee_List]![TermDate])
      )
   )

If you need more help, then break the failing one down further.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
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!
Helen FeddemaCommented:
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

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Nick67Commented:
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
GPSPOWAuthor Commented:
Breaking down the iif statements and testing helped me find the error:

A missing ")".

Thanks

Glen
Helen FeddemaCommented:
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)

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.