I want to use a lookup to select equation to apply..

[wesrockin]

Hello, first time posting on this Access forum. Thanks for looking!


I would like to find a more efficient way to apply equations within a query. I have data on tree diameters and heights, and I am applying 4 different equations in a nested IIf statement. Since I'm adding several more species, I was hoping to find a better way to apply these equations (more elegant), than making a super long and indecipherable IIf statement.

I was thinking of a Dlookup, but I don't know how to apply this to referencing an equation to apply, versus just pulling in a value.

Thank you for your time,

Jim

[June7]

You need to choose different equation depending on species? What changes in the equation - constants? Post the IIf expression for analysis. Could possibly need a table that associates species with the appropriate equation (or constants).

[wesrockin]

The equations and the coefficients both change, currently I have a a bunch of IIf statements using the first four characters to match up to equation and use the coefficients present in the [VolumeEquations] table e.g. [A1]. This works because there are only four possible equations across all the trees, but now there are going to be too many equations and these nested IIf statements are going to get unwieldy.

Thanks a bunch for your help!


VOLCFGRS: IIf([DBH]>=5,IIf(Left([Equation Reference],4)="Hahn",[VolumeEquations].[A1]*[Site_Index]^[VolumeEquations].[A2]*(1-Exp([VolumeEquations].[A3]*[DBH]^[VolumeEquations].[A4])),IIf(Left([Equation Reference],4)="McCl",IIf([SPFIA]<300,IIf([DBH]>=9,[VolumeEquations].[D1]+[VolumeEquations].[D2]*([DBH]^2)*[Ht],[VolumeEquations].[C1]+[VolumeEquations].[C2]*([DBH]^2)*[Ht]),IIf([DBH]>=11,[VolumeEquations].[D1]+[VolumeEquations].[D2]*([DBH]^2)*[Ht],[VolumeEquations].[C1]+[VolumeEquations].[C2]*([DBH]^2)*[Ht])),IIf(Left([Equation Reference],4)="Scot",([VolumeEquations].[A1]+([VolumeEquations].[A2]*[DBH]^[VolumeEquations].[A3])+(([VolumeEquations].[A4]*[DBH]^[VolumeEquations].[A5])*([MHT]^[VolumeEquations].[A6]))),0))),0)

You need to choose different equation depending on species? What changes in the equation - constants? Post the IIf expression for analysis. Could possibly need a table that associates species with the appropriate equation (or constants).

[June7]

That hurts just to read. I was thinking could have a table that associates species with the appropriate set of coefficients then include that table in the query. The criteria that determines coefficients is being extracted from longer value and that complicates the query structure. Another approach is a custom function.

If you want to provide db for analysis, follow instructions at bottom of my post.

[wesrockin]

Query expression showing wrong number of arguments when I try to insert the expression below into a query field. Any help would be greatly appreciated...

The IIf(Left[equationreference],4) is matching the 1st for letters of an equation name and pulling out the relevant column.

VOLCFGRS: IIf([DBH]>=5, IIf(Left([EquationReference],4)="Hann",([VolumeEquations].[A1]+[VolumeEquations].[A2]*[DBH]^2*[Ht])-((1/3)*(3.14159265*(4/12/2)^2)*(12.3427*4^.94927*[DBH]^-.35726)), IIf(Left([EquationReference],4)="McCl",IIf([DBH]>=11,0.500522+0.00167*[DBH]^2*[Ht],-.451948+.001921*[DBH]^2)*[Ht]),IIf(Left([EquationReference],4)="2008",IIf([DBH]<21,(.0810724804+ (.00198351037*[DBH]^ .0810724804)-((1/3)*(3.14159265*(4/12/2)^2)*(12.3427*4^.94927*[DBH]^-.35726)),0.237204154+(0.00221122919*[DBH]^0.237204154)-((1/3)*(3.14159265*(4/12/2)^2)*(12.3427*4^ .94927*[DBH]^-.35726)),IIf(Left([EquationReference],4)="East",IIf([DBH]<11, -.279035+.001894*([DBH]^2)*[Ht],0.248363+.001823*([DBH]^2*[Ht]))))),0)



Thanks in advance,

Jim

Query expression showing wrong number of arguments...

That hurts just to read. I was thinking could have a table that associates species with the appropriate set of coefficients then include that table in the query. The criteria that determines coefficients is being extracted from longer value and that complicates the query structure. Another approach is a custom function.

If you want to provide db for analysis, follow instructions at bottom of my post.

[June7]

This is the nesting as I see it (without the formulas for ease of viewing)

VOLCFGRS = IIf([DBH] >= 5, "this calc", _
IIf(Left([EquationReference], 4) = "McCl", IIf([DBH] >= 11, "this calc", "else this calc"), _
IIf(Left([EquationReference], 4) = "2008", IIf([DBH] < 21, "this calc", "else this calc"), _
IIf(Left([EquationReference], 4) = "East", IIf([DBH] < 11, "this calc", "else this calc")))))

If that is not correct (and it looks wonky to me) then fix the structure and add the formulas afterward.

With such a long expression, I would probably build a custom VBA function and call the function in query.