different types of currencies

[tommyried]

hello everyone,

I have different types of currencies in an excel file when I import it to access its all in us dollars.
my question is can I have different type of currencies in access also like in excel.

my fields are

US dollars
Euros

PO NO.	Unit price	Discount	total value
2212	$ 2,943.62	0	$ 58,872.40
2212	$ 1,015.00	0	$ 20,300.00
2212	$ 2,011.00	0	$ 40,220.00
2675	€ 7,500.00	0	€ 112,500.00
2675	€ 4,500.00	0	€ 90,000.00

[rpeare]

by default no, a field in a table is all one format type (say double). You would likely have to have as supplementary field that indicated the currency type, and another table that had the conversion rate (if it's appropriate to your process).

[tommyried]

what fields I need to use in new table apart from conversion rate ?and how do I calculate total?

[June7]

What you import is just a number. Access doesn't 'know' it's Pounds or Dollars (nor does Excel). Suggest you not format the table fields as a currency type. Set formatting in forms and reports.

One approach is to have another column in the Excel file that has a value to categorize the record as Pounds or Dollars or whatever. How many currency types are possible? If only those two then a table is not mandatory although might be helpful. Create a field in query with a conditional expression that calculates the conversion if needed:

UnitPrice * IIf([CurrType]="Pounds", 0.9, 1)

0.9 is just for example. I have no idea what the exchange rate is. The direction of the conversion calc depends if you want the result in Pounds or Dollars.

However, considering the volatility of exchange rates, might not want to hard code it into the expression. Make the expression dynamic by either referencing a textbox on form where user enters the rate or use a table that has the rate for each currency type. At a minimum fields would be CurrType and Rate. Join this table to your imported data table in query by linking on the CurrType fields. Then expression could simply be:

UnitPrice * Rate

Another consideration is whether or not the actual rate, not just the CurrType, should be in the record. I expect the exchange rate that should be used is the rate in effect at the time of the transaction. This means the rate must be saved in order to avoid using the March 2014 rate to calculate records from July 2012.