Query iif calc too long

[seocavaz]

Hi,

I have a field in a query that needs to run this calculation:

CalcdPOF: (IIf([rsr]<=[Tbl_FPHJEDdata].[y1], 9, IIf([rsr]<=[Tbl_FPHJEDdata].[y2],10^(Log([Tbl_FPHJEDdata].[x1])+(Log([Tbl_FPHJEDdata].[x2])-(Log([Tbl_FPHJEDdata].[x1]))/([Tbl_FPHJEDdata].[y2]-[Tbl_FPHJEDdata].[y1])*([RSR]-[Tbl_FPHJEDdata].[y1],IIF([RSR]<= [Tbl_FPHJEDdata].[y3],10^(LOG([Tbl_FPHJEDdata].[x2])+(LOG([Tbl_FPHJEDdata].[x3])-LOG([Tbl_FPHJEDdata].[x2]))/( [Tbl_FPHJEDdata].[y3]-[Tbl_FPHJEDdata].[y2])*([RSR]-[Tbl_FPHJEDdata].[y2])),IIF([RSR]<= [Tbl_FPHJEDdata].[y4],10^(LOG([Tbl_FPHJEDdata].[x3])+(LOG([Tbl_FPHJEDdata].[x4])-(LOG([Tbl_FPHJEDdata].[x3])))/( [Tbl_FPHJEDdata].[y4]-[Tbl_FPHJEDdata].[y3])*([RSR]-[Tbl_FPHJEDdata].[y3])),IIF([RSR]<= [Tbl_FPHJEDdata].[y5],10^(LOG([Tbl_FPHJEDdata].[x4])+(LOG([Tbl_FPHJEDdata].[x5])-(LOG([Tbl_FPHJEDdata].[x4])))/( [Tbl_FPHJEDdata].[y5]-[Tbl_FPHJEDdata].[y4])*([RSR]-[Tbl_FPHJEDdata].[y4])),IIF([RSR]<= [Tbl_FPHJEDdata].[y6],10^(LOG([Tbl_FPHJEDdata].[x5])+(LOG([Tbl_FPHJEDdata].[x6])-(LOG([Tbl_FPHJEDdata].[x5])))/( [Tbl_FPHJEDdata].[y6]-[Tbl_FPHJEDdata].[y5])*([RSR]-[Tbl_FPHJEDdata].[y5])),IIF([RSR]<= [Tbl_FPHJEDdata].[y7],10^(LOG([Tbl_FPHJEDdata].[x6])+(LOG([Tbl_FPHJEDdata].[x7])-(LOG([Tbl_FPHJEDdata].[x6])))/( [Tbl_FPHJEDdata].[y7]-[Tbl_FPHJEDdata].[y6])*([RSR]-[Tbl_FPHJEDdata].[y6])),IIF([RSR]<= [Tbl_FPHJEDdata].[y8],10^(LOG([Tbl_FPHJEDdata].[y7])+(LOG([Tbl_FPHJEDdata].[x8])-(LOG([Tbl_FPHJEDdata].[x7])))/( [Tbl_FPHJEDdata].[y8]-[Tbl_FPHJEDdata].[y7])*([RSR]-[Tbl_FPHJEDdata].[y7])),IIF([RSR]<= [Tbl_FPHJEDdata].[y9],10^(LOG([Tbl_FPHJEDdata].[x8])+(LOG([Tbl_FPHJEDdata].[x9])-(LOG([Tbl_FPHJEDdata].[x8])))/( [Tbl_FPHJEDdata].[y9]-[Tbl_FPHJEDdata].[y8])*([RSR]-[Tbl_FPHJEDdata].[y8])),IIF([RSR]<= [Tbl_FPHJEDdata].[y10],10^(LOG([Tbl_FPHJEDdata].[x9])+(LOG([Tbl_FPHJEDdata].[x10])-(LOG([Tbl_FPHJEDdata].[x9])))/( [Tbl_FPHJEDdata].[y10]-[Tbl_FPHJEDdata].[y9])*([RSR]-[Tbl_FPHJEDdata].[y9])),IIF([RSR]<= [Tbl_FPHJEDdata].[y11],10^(LOG([Tbl_FPHJEDdata].[x10])+(LOG([Tbl_FPHJEDdata].[x11])-(LOG([Tbl_FPHJEDdata].[y10])))/( [Tbl_FPHJEDdata].[y11]-[Tbl_FPHJEDdata].[y10])*([RSR]-[Tbl_FPHJEDdata].[y10])),2100)))))))))))))

When I do just the first couple of iif statements it works fine however it gives me a syntax error when I have them all in there, though I have combed through and there is no errors. Similarly when I tried to do it through the "build" tool, it will only let me get to a certain amount of characters, is that why? Have I exceeded the allotted amount of characters?

If anyone has an idea of a simpler way of doing it, that may help too.

Thanks in advanced!!

Sam

[Perceptus]

Do you have the fomulae prior to to the nested IFF. Ive done the same thing in the past, the nested IIF was kludge for needing to properly query the data with a bunch of sub queries.

[JoeM]

Anything that complex should be done as a User Defined Function (UDF) in VBA. They are much easier to program, follow, and maintain. Trying to maintain/support a function like that where everything runs together is extremely difficult.

Here are some links on UDFs:
http://datapigtechnologies.com/blog/...ons-in-access/
http://www.fontstuff.com/vba/vbatut04.htm

[seocavaz]

The formula would be:

10^(log(Xn)+(log(Xn+1)-log(Xn))* ([rsr]-Yn)/(Yn+1 -Yn)

So basically I should break it down into 11 different fields then find the one not null?

[JoeM]

So you are checking 11 different values?
Where are these coming from?
Will the results of 10 of these 11 always be null?

[Perceptus]

if that is the formula used on all data you could probably get a really nice recursive calculation on nulls to keep code size down.

[June7]

Do you really need the table prefix?

I think missing a paren:

CalcdPOF: (IIf([rsr]<=[Tbl_FPHJEDdata].[y1], 9, IIf([rsr]<=[Tbl_FPHJEDdata].[y2],10^(Log([Tbl_FPHJEDdata].[x1])+(Log([Tbl_FPHJEDdata].[x2])-(Log([Tbl_FPHJEDdata].[x1]))/([Tbl_FPHJEDdata].[y2]-[Tbl_FPHJEDdata].[y1])*([RSR]-[Tbl_FPHJEDdata].[y1]),IIF([RSR]<= [Tbl_FPHJEDdata].[y3],10^(LOG([Tbl_FPHJEDdata].[x2])+(LOG([Tbl_FPHJEDdata].[x3])-LOG([Tbl_FPHJEDdata].[x2]))/( [Tbl_FPHJEDdata].[y3]-[Tbl_FPHJEDdata].[y2])*([RSR]-[Tbl_FPHJEDdata].[y2])),IIF([RSR]<= [Tbl_FPHJEDdata].[y4],10^(LOG([Tbl_FPHJEDdata].[x3])+(LOG([Tbl_FPHJEDdata].[x4])-(LOG([Tbl_FPHJEDdata].[x3])))/( [Tbl_FPHJEDdata].[y4]-[Tbl_FPHJEDdata].[y3])*([RSR]-[Tbl_FPHJEDdata].[y3])),IIF([RSR]<= [Tbl_FPHJEDdata].[y5],10^(LOG([Tbl_FPHJEDdata].[x4])+(LOG([Tbl_FPHJEDdata].[x5])-(LOG([Tbl_FPHJEDdata].[x4])))/( [Tbl_FPHJEDdata].[y5]-[Tbl_FPHJEDdata].[y4])*([RSR]-[Tbl_FPHJEDdata].[y4])),IIF([RSR]<= [Tbl_FPHJEDdata].[y6],10^(LOG([Tbl_FPHJEDdata].[x5])+(LOG([Tbl_FPHJEDdata].[x6])-(LOG([Tbl_FPHJEDdata].[x5])))/( [Tbl_FPHJEDdata].[y6]-[Tbl_FPHJEDdata].[y5])*([RSR]-[Tbl_FPHJEDdata].[y5])),IIF([RSR]<= [Tbl_FPHJEDdata].[y7],10^(LOG([Tbl_FPHJEDdata].[x6])+(LOG([Tbl_FPHJEDdata].[x7])-(LOG([Tbl_FPHJEDdata].[x6])))/( [Tbl_FPHJEDdata].[y7]-[Tbl_FPHJEDdata].[y6])*([RSR]-[Tbl_FPHJEDdata].[y6])),IIF([RSR]<= [Tbl_FPHJEDdata].[y8],10^(LOG([Tbl_FPHJEDdata].[y7])+(LOG([Tbl_FPHJEDdata].[x8])-(LOG([Tbl_FPHJEDdata].[x7])))/( [Tbl_FPHJEDdata].[y8]-[Tbl_FPHJEDdata].[y7])*([RSR]-[Tbl_FPHJEDdata].[y7])),IIF([RSR]<= [Tbl_FPHJEDdata].[y9],10^(LOG([Tbl_FPHJEDdata].[x8])+(LOG([Tbl_FPHJEDdata].[x9])-(LOG([Tbl_FPHJEDdata].[x8])))/( [Tbl_FPHJEDdata].[y9]-[Tbl_FPHJEDdata].[y8])*([RSR]-[Tbl_FPHJEDdata].[y8])),IIF([RSR]<= [Tbl_FPHJEDdata].[y10],10^(LOG([Tbl_FPHJEDdata].[x9])+(LOG([Tbl_FPHJEDdata].[x10])-(LOG([Tbl_FPHJEDdata].[x9])))/( [Tbl_FPHJEDdata].[y10]-[Tbl_FPHJEDdata].[y9])*([RSR]-[Tbl_FPHJEDdata].[y9])),IIF([RSR]<= [Tbl_FPHJEDdata].[y11],10^(LOG([Tbl_FPHJEDdata].[x10])+(LOG([Tbl_FPHJEDdata].[x11])-(LOG([Tbl_FPHJEDdata].[y10])))/( [Tbl_FPHJEDdata].[y11]-[Tbl_FPHJEDdata].[y10])*([RSR]-[Tbl_FPHJEDdata].[y10])),2100)))))))))))))

And the outermost paren pair not needed.

I think there is a limit of 7 nested IIf.

Consider Switch() function or VBA.

[CJ_London]

to reduce the length of the iif statement and perhaps make it easier to read, alias your Tbl_FPHJEDdata table to something much shorter -e.g. D or even remove it if the y1,y2.. field names are unique