Currency Conversion

[BCre8iv]

I have a dilemma!!! I need to know how to make my tables relate so that information drawn from a table can be compared to data in another table and converted to Canadian dollars based on that data. In other words if one of my customers is Canadian I need to invoice that client in Cdn dollars. Conversly if one of my vendors is Amercian I need to convert the price I pay for a product to Canadian. And vice versa. I know this seems a little vague if someone has an example that is close to this or script that can do this it would be greatly appreciated. Thx in advance.
Tina

 

[allanon]

Here is what I did. You will need a conversion rate. I set up the following table for that:

Misc

Key FieldDesc FieldValue
1 ConvRate 1.49

Then I set up a query:

ConversionRate

SELECT Misc.FieldValue AS Rate
FROM Misc
WHERE (((Misc.FieldDesc)="ConvRate&quot);

Which gives me:

Rate
1.4895

Next is to get some test data:

Key Invoice_ID Total Price IncVAT Total Price ExVAT
5 1 $20.15 $33.15
6 2 $98.05 $122.55
7 3 $95.25 $114.75

After that use this query:

SELECT Invoices.*,
(Invoices.[Total Price IncVAT]*ConversionRate.[Rate]) AS UsAmt1,
(Invoices.[Total Price ExVAT]*ConversionRate.[Rate]) AS UsAmt2
FROM Invoices, ConversionRate;

Result is:

Key Invoice_ID Total Price IncVAT Total Price ExVAT CanAmt1 CanAmt2
5 1 $20.15 $33.15 30.013425 49.376925
6 2 $98.05 $122.55 146.045475 182.538225
7 3 $95.25 $114.75 141.874875 170.920125


And there you have it.

 

[BCre8iv]

Thx, I will try that
Tina