Wayne,
If there is no set logic (and you haven't established any yet) between the result of
the "digit" calc and the final amount, that's a different problem.
I think the data entry form for your table is the place to handle this. Then, the
report would just be a matter of displaying those values. I'll make that "form" argument
later...
I would consider creating a new lookup table with 2 fields. (ex. tblRates)
CalcValue Multiplier
1 540
2 720
3 1080 etc..
To the original form table, I would add 2 fields Multiplier and FinalAmount.
On the data entry form, on the AfterUpdate event of any element of the original
calculation is entered/edited, calculate and capture the resulting values to Multiplier
and FinalAmount, using the value from a DLookup against the table, to get the correct
multiplier for the FinalAmount.
The reason why we would want to capture the Multiplier and FinalAmount result is that
the values in your Rate table may change in the future, so any "on the fly" historical
record calculations would be affect by any subsequent cahnges in tblRates.
We don't want that...
As a general rule never capture calculated amounts... however in certain cases it is
imperative. Such is the case with product Prices that are constantly changing. We must
save all the values that were effect at the time of the purchase.
And, since we want to capture that Multiplier and FinalAmount value, the best place do
that is on the original data entry form... not in the report query.
Now just pass your table values directly to the report...
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Wayne Viles said:
Thanks Al, If it were only that simple. The results of the calculation are
not as they first seem and are not multiples of 360 (although some are). If
the result of the Expr field is 1 the figure I wish to show is £540. If 2,
the figure will be £720, if 3, the figure will be £1080 and so on. Can I
use the IIf prefix in a calculated field using something like IIf [Expr1]
=<1,1*540, Iff [Expr1] =<2,2*720
Al Campagna said:
Wayne,
Well It appears as though your taking the result of your calculation, and
multiplying
by 360 ??
Some previous calculation = 1 * 360 = 360
or =2 * 360 = 720
or =10 * 360 = 3600 etc...
Then just concatenate the "£" (usually an Alt-0163) with the original calculation
multiplied by 360.
Some Results: Chr(0163) & Format(((your original calculation) * 360),"###")
or more "normally"...
Some Result : Format((your original calculation)*360, "£#,##0")
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
