currency

Hi Guys,
Could you please advise if there is a possibility of changing the currency in datasheet subform. For example I need to raise a Purchase Order in Euros instead of GBP (GBP is set up as currency) when I try to change only one PO it changes all of them to EUR....help:P

re: currency

If you're asking if you can change the currency formatting for a field in some records, but not in all records, the answer is No. The only way to conditionally format a field in a datasheet view is to use Format - Conditional Formatting option from the menu, and this doesn't allow for formatting such things as currency formatting.

The only work around I can think of, assuming that there's a condition of a field that differentiates the Euro records from the GBP records, would be to use this condition in the above mentioned Format - Conditional Formatting option to set the the background color depending on whether it's Euro or GBP, dropping the currency formatting completely and simply listing the amount.

Linq ;0)>

re: currency

Hi. Further to Linq's reply, there is another issue which you may not have considered; if you are dealing with Purchase Orders you need to consider the current exchange rate, which is of course different for the currencies mentioned.

Whilst this has nothing whatsoever to do with formatting, it has a very real impact on accounting for budgeted vs actual spend, where currency conversion will be essential.

For example, the Euro is currently trading at 0.791 GBP, the US dollar at 1.9625 and so on.

If you are recording only Euros and GBP you will also need to consider whether you need to take into account local rates of VAT which may be charged in other European countries that would invalidate the routine 17.5% standard / 5% low rate/ 0 % exempt rates applicable in the UK.

For these reasons I would suggest that you really need to hold a currency type table to establish for each transaction the currency involved, and to record the currency type as part of the purchase order - with GPB the default. Then you can apply custom formatting dependent on the currency, and include the exchange rate ruling on the transaction date concerned.

-Stewart

re: currency

Thank you for help.
The prices don't need invertion to another currency.
Is there not possible to run a code in VBA for that???

re: currency

Unless the table design of the database specifically accounted for the need to record prices in other currencies there would be little point in running VBA code behind the scenes. In your application you do not need to do this - but if you had it is necessary to store the conversion rate along with the transaction. Exchange rates vary throughout the day in real time, so running an exchange rate conversion function in VBA will not recover the exchange rate that was applicable at the time of the transaction!

-Stewart