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" <(E-Mail Removed)> wrote in message
news:E568AA04-879D-40C8-9E1B-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
>>
>> "Wayne Viles" <(E-Mail Removed)> wrote in message
>> news:5C2AF976-D8A8-4E86-8A29-(E-Mail Removed)...
>> >I have a calculated Expr field in a query. The result of the calculation
>> > needs to be shown as a set amount. For example, if the result of the
>> > calculation is 2, the figure I require to be shown is £720. If the result is
>> > 3, the figure to be shown is £1080 and so on. How can I achieve this using
>> > the Expr calculation.
>>
>>
>>
Similar Threads
