iif statement and defining calculated expression once only

[Ruth]

iif (complicated expression<0,0,original complicated expression) - when
writing iif statements of this format as a field in an access query, can I
avoid defining the complicated expression twice. i.e I want to somehow call
the complicated expression by a temp field name e.g. x. I know I could do
this by putting the complicated expression in a second field, but somehow I
feel it would be neater if all in the same field.
e.g. in the below how can I only need to define the complicated expression
,[Net Capacity]-[max workplace]*([permanent Capacity]/([Permanent
Capacity]+[Temporary Capacity])) once?

secTempinNC: IIf([sort]="PR",0,IIf([Net Capacity]-[max
workplace]*([permanent Capacity]/([Permanent Capacity]+[Temporary
Capacity]))<0,0,[Net Capacity]-[max workplace]*([permanent
Capacity]/([Permanent Capacity]+[Temporary Capacity]))))

 

[Dale_Fye via AccessMonster.com]

I generally do it by writing a function, and having the function return a
value. Something like:

Public Function fnCompExpr(NetCapacity as long, MaxWorkplace as long,
PermCap as Long, TempCap as long)
as long

fnCompExpr = NetCapacity - MaxWorkplace*(PermCap/(PermCap+TempCap))

if fnCompExp < 0 then fnCompExp = -1

End Function

Then, just call the function in your query, and pass it the appropriate field
values.

Dale

iif (complicated expression<0,0,original complicated expression) - when
writing iif statements of this format as a field in an access query, can I
avoid defining the complicated expression twice. i.e I want to somehow call
the complicated expression by a temp field name e.g. x. I know I could do
this by putting the complicated expression in a second field, but somehow I
feel it would be neater if all in the same field.
e.g. in the below how can I only need to define the complicated expression
,[Net Capacity]-[max workplace]*([permanent Capacity]/([Permanent
Capacity]+[Temporary Capacity])) once?

secTempinNC: IIf([sort]="PR",0,IIf([Net Capacity]-[max
workplace]*([permanent Capacity]/([Permanent Capacity]+[Temporary
Capacity]))<0,0,[Net Capacity]-[max workplace]*([permanent
Capacity]/([Permanent Capacity]+[Temporary Capacity]))))

 

[vanderghast]

The query optimizer *should* be able to recognize that the same expression
is used twice, and *should* cache it after its first computation, but
definitively, YOU will have to type it twice, or to use an expression alias,
in another column, and use the alias rather than the expression, in the
SELECT clause.

Vanderghast, Access MVP