Can you help me make this string shorter?

A

Alberta Rose

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
 
J

Jack Leach

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 said:
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
Click to expand...
 
J

John W. Vinson

I get an error message saying that my string is too complex. Can anyone see
a way to streamline it??
Click to expand...

Two suggestions:

1. Use the Switch() function rather than nested IIF's. Switch() takes
arguments in pairs, and evaluates them left to right; when it first encounters
a pair in which the first element is TRUE, it returns the second element of
the pair and quits. So:

=Switch(IsNull([costcode] OR IsNull([costtype], 0,
[costcode] = "013210" AND [costtype] = "05320",
[txtEstimatedCost]/[txtCraftLabEst],

<etc>

2. and much better: normalize your tables so that you don't have to store so
much business logic in complex code!!! I don't clearly understand the business
logic but it certainly seems like you have a whole lot of field ending in Est,
which probably should not be fields in your table, but *records* in another
table (which would also have costtype and costcode fields so it could be
simply joined to this table in a Query).
=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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

if then else statement 6

Top