Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 25th, 2003, 04:15 AM
Registered User
 
Join Date: Jul 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default query too complex

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


 
Old August 26th, 2003, 11:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


 
Old August 28th, 2003, 02:01 AM
Registered User
 
Join Date: Jul 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old August 28th, 2003, 09:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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








Similar Threads
Thread Thread Starter Forum Replies Last Post
complex query... help plz hello MySQL 1 April 28th, 2007 05:48 AM
complex query g_vamsi_krish SQL Language 3 February 27th, 2006 10:48 AM
Help need with designing this complex query method Access 2 July 1st, 2005 05:29 AM
COMPLEX QUERY PROBLEM nikosdra Classic ASP Databases 2 July 28th, 2003 02:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.