Hi all
I have created a query which sets a target per quarter after evaluating the value (eg: [V2000q4]) and target (eg: [Target 2000q4])for the previous quarter and then adding any additional transactions (eg: [T2001q1]). ie:

IIf([V2000q4]>[Target2000q4],Nz([V2000q4],0)+Nz([T2001q1],0),Nz([Target2000q4],0)+Nz([T2001q1],0)) AS Target2001q1,

IIf([V2001q1]>[Target2001q1],Nz([V2001q1],0)+Nz([T2001q2],0),Nz([Target2001q1],0)+Nz([T2001q2],0)) AS Target2001q2,

IIf([V2001q2]>[Target2001q2],Nz([V2001q2],0)+Nz([T2001q3],0),Nz([Target2001q2],0)+Nz([T2001q3],0)) AS Target2001q3,


The query works fine until I repeat the above expression too many times, then I get an error message saying "query too complex". I am sure that if I wrote this as code it would work, but I am not sure how to write it. Can anyone help?
Thanks
Liz

Per Microsoft:

Replace the IIF function with an If...Then...Else statement.

Remarks

Visual Basic supports the IIF function as an alternative to the If...Then...Else statement. For example, the following code evaluates the statement 'Price > 10.00' and calls the function Expensive if the expression is true, or Inexpensive if it is False:


Function CheckPrice(ByVal testMe As Double) As Object
    IIf(price > 10.00, Expensive, Inexpensive)End Function

Although this may seem like a shortcut to using the If...Then...Else statement, IIF works somewhat different than If...Then...Else. IIF statements must evaluate the entire statement when preparing the argument. This can lead to undesirable side effects.

For instance, if the expression in the arguments list includes function calls, the function in the False statement will be called even if the expression evaluates True.

Resolution

Consider replacing the IIF statement with If...Then...Else statement. For instance, you could change the example above to:

Function CheckPrice(ByVal testMe As Double) As Object
    If price > 10.0 Then
        Expensive
    ElseIf price < 10.0 Then
        Inexpensive
    Else
        MsgBox("The price is not valid.")
    End If
End Function

HTH,

Bob

Hi Bob
Thanks for your help. I will try and implement it and will let you know how it works.
Regards
Liz

Hi Liz,

Also be aware, if you aren't already, that you can nest multiple If...Then...Else blocks, and by the looks of your code you may need to:

If <contition1> Then
  If <condition2> Then

  'executable code

  End If
End if

Regards,

Bob