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?