Expression recalculates fields

[Guest]

I am having trouble with a query field (field 1) that is calculated using
other fields (field 2 & field 3) in the query. The problem is that the other
fields (field 2 & field 3) are the returns from a function. It looks like
when field 1 is calculated, it first recalculates the values for field 2 and
field 3. The problem I have is that the functions that generate field 2 and
field 3 utilize static variables as counters (sort of), and, therefore,
return different values when recalculated. The only thing I can think of is
create yet another function or two to store the values from field 2 and field
prior to calculating field 1. Does anyone out there know of an alternative?

Thanks,

Keith

 

[JethroUK©]

not exactly sure of your situation - but appropriate use of bracketing
prioritises calculations


| I am having trouble with a query field (field 1) that is calculated using
| other fields (field 2 & field 3) in the query. The problem is that the
other
| fields (field 2 & field 3) are the returns from a function. It looks like
| when field 1 is calculated, it first recalculates the values for field 2
and
| field 3. The problem I have is that the functions that generate field 2
and
| field 3 utilize static variables as counters (sort of), and, therefore,
| return different values when recalculated. The only thing I can think of
is
| create yet another function or two to store the values from field 2 and
field
| prior to calculating field 1. Does anyone out there know of an
alternative?
|
| Thanks,
|
| Keith
|

 

[Guest]

I think the following demonstrates my dilemma.


SQL:
SELECT [PART MASTER].[REF #], randomtest() AS random1, [random1] AS Expr1
FROM [PART MASTER]
ORDER BY [PART MASTER].[REF #];

---------------------
'Code for randomtest function
Function randomtest()
randomtest = Rnd
End Function

------------------------------
results of query

REF # random1 Expr1
0 0.8626193 0.79048
1429 0.8626193 0.79048
5760 0.8626193 0.79048

As you can see, Expr1 is not the value in field random1 as I expected.
Instead, it appears to re-run the randomtest function. Does anyone know if I
can control this behavior?

Thanks,

Keith