Working with formulas...new post!!!

[Guest]

I was very vague on my previous post so here are more details...Please
remember I don't have a lot of experience with access!.

I have three columns in my table like

C1 C2 C3
USD 1000 X
SGD 200 X
GBP 500 X
AUD 200 X

So I’ll need to “add a formula†in C3 to display specific value (currency)
dependng on the text entered on C1, then I'll have to multiply values in C2
for a determined rate so it'd be something like this???:

IF([C1]=â€USDâ€,[C2]*.98)OR ([C1]=â€SGDâ€,[C2]*.50)OR ([C1]=â€AUDâ€,[C2]*2,0)OR
([C1]=â€USDâ€,[C2]>>>>For USD C3 would have to be equal to C2

I know how to do this with Excel, but I don’t even know if this can be done
with access. Maybe running a query? I’d do the table again and paste it from
Excel but it has more than 600,000 records and it’ll be a pain.

Please help!

 

[Guest]

You have to nest the IIFs like this ---
IF([C1]=â€USDâ€,[C2]*.98), IIF([C1]=â€SGDâ€,[C2]*.50), IIF([C1]=â€AUDâ€,[C2]*2)
IIF([C1]=â€USDâ€,[C2], "Error"))))

A better way if you have lots of these conparrisons is to build a
translation table.
Currency Exchange
USD .98
SGD .5
AUD 2
Then in your query join your data table to the translation table on [C1] to
[Currency] field in a Left join (just in case you omitted something in the
translation table.
Your calculations would be [C2] * [Exchange] AS Value_Rate.

 

[Jeff Boyce]

It would be unusual to store a calculated value in an Access table -- Access
is not Excel.

If you need to calculate a value based on other values, use a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Linq Adams via AccessMonster.com]

Actually, if the calculated value is dependent on the current exchange rate
(which the question sort of sounds like) then this may be one of the cases
where storing the result is warrented. Recalculaing it at a later date when
the exchange rate has changed would not give accurate results. You could, of
course, go thru the dog and pony show of having another table to track the
historical exchange rates and so forth, but in this day of gazillion gigabyte
hard drives it seems like overkill to avoid stoing a few calculated results.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com