Results 1 to 4 of 4
  1. #1
    dragon232 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Posts
    24

    "Too complex query" for a Simple Select Query

    Hi, I get the error message that my query is too complex, and I haven't been able to figure out what I should do. The query takes the fields of a "Salaries" and "Tax" table (containing information about each employee - how much they worked, and so forth - and the tax table about the taxes that have to be paid), and is meant to perform all the salary related calculations for all employees. As there are many taxes (some paid from a company account, others paid from an employee account, but the company has to pay both), there are many different fields in the query. The idea behind the query is that my boss wants to enter the after-tax salary (the one the worker actually receives in hand - call it VN), and get the pre-tax salary (VB) [this is in a form based on this query I'm talking about]. The big problem with this, is that depending on the absolute value of VB, the way VN is calculated changes. Thus I have 4 different ways to calculate VN from VB. This means that given a random VN, I will have four possible values for VB: call them VB1, VB2, and so forth. What I have to do to make it work is a formula of the following kind for VB:



    VB: IIf(0<[VB1] And [VB1]<=1500, [VB1], IIf(1500<[VB1] And [VB2] <=3000, [VB2], IIf(3000<[VB2] And [VB3]<=12000, [VB3], IIf([VB3]>12000, [VB4], 0))))

    Now if I run the query, I get the "too complex" error. So I've tried a few things. If I write VB:IIf(0<[VB1] And [VB1]<=1500, [VB1], 0) only, then my query works, even if I also make this amended query calculate a field called VBTest: (Multiple If Function here). It seems that only when many further calculations have to be performed on the (Multiple If function) the query error comes up. I also tried breaking up the big if function into 4 separate functions and then adding them up in a different field. Still get the error. I also tried using a:

    VB: [VB1]*CBool(conditions)*(-1)+[VB2]*CBool(conditions)*(-1)+...

    Still gives the same error. How can I approach this? It seems very clear that it's having further calculations using the result of the multiple if function that actually is causing the problem. And yet how can I get around this if my calculation requires me to do this?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if youre going to use lots of IIFs, instead create a lookup table or a custom function get your results.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I would create a VBA function and call it - if you don't have too many records. Using VBA is so much easier to maintain and troubleshoot.

  4. #4
    dragon232 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Posts
    24
    Quote Originally Posted by ranman256 View Post
    if youre going to use lots of IIFs, instead create a lookup table or a custom function get your results.
    Quote Originally Posted by aytee111 View Post
    I would create a VBA function and call it - if you don't have too many records. Using VBA is so much easier to maintain and troubleshoot.
    Hi Guys, thanks for the suggestion. I have just implemented it, and it works! Thanks a lot!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-16-2016, 10:11 AM
  2. Replies: 3
    Last Post: 03-14-2016, 05:10 AM
  3. Replies: 1
    Last Post: 10-08-2012, 09:01 AM
  4. "Query to complex"
    By Epona in forum Queries
    Replies: 3
    Last Post: 02-06-2012, 09:49 AM
  5. a simple "display date " query
    By Ushera in forum Queries
    Replies: 2
    Last Post: 07-31-2009, 06:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums