Hi Laurie,
I find that rather than using a massive amount of nested Iif statements in
source string or a query, sometimes you're better off creating a public
function that will handle the crunchwork for you. I haven't deciphered
exactly what you're trying to accomplish here, but it looks like this might
be a good canidate for the concept.
In a standard module, declare a function as Public and you can then have it
return the required value (what your Iif statements would otherwise
ultimately return). Reference it in a control source or query just like you
would a builtin function: =FunctionName(arg arg arg arg).
At quick glance at your calculation, it looks like you would pass a number
of the fields as arguments to the function. Then you can convert all of your
Iifs into the more code friendly If/Then/Else statements inside the function.
It might take a lot of If/Then statements, but will probably be more
efficient than the way you are going about it now.
Note that in order to return a function as Null the datatype of the function
needs to be declared as a Variant.
Public Function GetThisValue( _
sCostType As String, _
cEstimatedCost As Currency, _
cFirewatchLabEst As Currency, _
etc etc) As Variant
'do all your calculations here and return the
'appropriate value to the function
End Function
and call it from your control source or query like so:
=GetThisValue([costtype], [txtEstimatedCost], etc)
hth
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
"Alberta Rose" wrote:
> I get an error message saying that my string is too complex. Can anyone see
> a way to streamline it??
>
> =IIf(IsNull([costcode]) Or IsNull([costtype]),0,IIf([costcode]="013210" And
> [costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110"
> And
> [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201"
> And
> [costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301"
> And
> [costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([costcode]="061101"
> And
> [costtype]="05310",[txtEstimatedCost]/[txtSiteTeamLabEst],IIf([costcode]="031101"
> And
> [costtype]="05110",[txtEstimatedCost]/[txtOfficeTeamLabEst],IIf([costcode]="042211"
> And
> [costtype]="05110",[txtEstimatedCost]/[txtEngLabEst],IIf([costcode]="045311"
> And
> [costtype]="05130",[txtEstimatedCost]/[txtThirdPartyLabEst],IIf([costcode]="032101"
> And [costtype]="05110",[txtEstimatedCost]/[txtProcurementLabEst],
> IIf([costcode]="061103" And
> [costtype]="05110",[txtEstimatedCost]/[txtClericalLabEst],
> IIf([costcode]="032101" And
> [costtype]="05130",[txtEstimatedCost]/[txt3rdPtyProcLabEst] ,
> IIf([costcode]="045311" And
> [costtype]="05110",[txtEstimatedCost]/[txtEngMgmtLabEst],
> IIf([costcode]="061201" And
> [costtype]="05320",[txtEstimatedCost]/[txtHSSEEst], IIf([costcode]="064101"
> And [costtype]="05320",[txtEstimatedCost]/[txtSiteConMgmtEst],
> Null)))))))))))))))
>
> Thanks, Laurie
Similar Threads
