Euro Exchange Rate

[Fozi]

I'm developing a project database for a client. Projects are priced in sterling but converted into Euro. How do I set this up so that each value within the Euro field stays at the rate it was calculated on entry and does not change as the rate is updated?

Thanks
Fozi

 

[Sum Guy]

I'm developing a project database for a client. Projects are priced in sterling but converted into Euro. How do I set this up so that each value within the Euro field stays at the rate it was calculated on entry and does not change as the rate is updated?

Thanks
Fozi

Personally, I would demand the British government get off their high horse and join the Euro parade.

Sorry, that was probably less than helpful but I couldn't resist.

 

[statsman]

It is generally frowned upon, but this may be an exception to an Access rule that you never store a calculated value in a table.
Add a new text box to your form and name it "Rate". Rate will be used to calculate the exchange rate for your amount.
In the Euro text box in the Control Source enter the following:

=([Amount]*[Rate])


where [Amount] is the amount being charged in pounds. Once you enter an amount in sterling and enter that days rate in the Rate text box, you should have the amount in Euros calculated automatically.

I've never tried this specific function before so you may have to play with it a bit.

 

[Fozi]

Thanks Statsman

Thats kinda where I was going with it. However I want the Rate value to populate dynamically from the rates table picking the one marked current.

 

[GaryPanic]

ok have it look at your rate table once only
so default rate at 1.5 to the pound then allow to be over written - store the rate in your table .

personally i have to keep sterling /euros /US dollars seperate - but i does depend on your business -- if you in insurance , then lloyds will accept USD/EUR and GBP as sepearate currencies - if you mingling with other currencies then convert these in a base currency (I keep USD/EUR clean - so i know that any exchange rates flux can only be done in sterling ...)

 

[DCrake]

When creating applications that deal with multi currencies it is the norm to hold the following information about each Purchase/Sale

Date
Currency Type
Conversion Rate
Net amount
Vat Rate
Decimal Places
Shipping
Discount rate

This is held at order level only.

Because we know what the net value is and the currency we are dealing with we can suffix the cost with the correct symbol $, £, Euro, etc.

So we take the net and multiply it by the rate then round it to the desired decimal place.

Calculate the VAT based on the vat rate at point of sale.

Add all the other items, where appropriate to reach the gross rate. I have included discount rate as a discount rate can change for customers from time to time. Don't rely on the current rate being accurate.

I remember alot of years age (80's) when EPOS software in the UK was hard coded with the VAT rate. The chancellor in his infinate wisdom decided to change the rate. All the software houses had to rewrite their software to accommodate variable rates. During this period shops were pretending to offer customers goods and services at the old VAT rate. Whereas infact they had to do it because their systems could not calculate the VAT correctly.

CodeMaster:

 

[Fozi]

Sorry Folks, really struggling with this. Can anyone post a sample database showing how to achieve this.

Basically I want an automated function which calculates and stores euro values from Sterling which remain static once calculated according to the exchange rate at the time of entry.

Thanks for any help/time.

Fozi

 

[DCrake]

In your mdb you will need a table to hold the current exchange rate between Euro and Sterling.

On your form that collects the data you will need a field that you enter the GBP in then have another field that holds the euro value this will be automatically populated on the AfterUpdate of the GBP field. Or you can set the default value to the Euro field to be GBP*EuroRate.

In the underlying table you will need a field to store the GBP value, the Euro Rate. Then when you create reports you can simply create a calculated field based on the two entities.

Tip: In the Euro rate field in the table set the default value for new records to be 1. So that if you try to do a calculation with a null or zero value you will most likely get errors. By setting it to 1 you will still get the same value if the order was not in euros.

CodeMaster:

 

[Fozi]

Thanks DCrake

That's not a million miles from where I'm at. But how do I stop the historical records updating every time the Euro exchange rate is amended?

 

[gemma-the-husky]

I would be inclined to store the applicable euro rate along with every sterling value (alternativley store a euro rate table with changes, then look up the euro rate as and when you need it) In this case, as the euro-rate shouldn't change once you store it, I would be inclined to store the effective rate, for subsequent efficiency/clarity - even though this could be considered as non-normalised )

that way you can either

a) report in sterling
b) report in euros at any arbitrary rate
c) report in converted euros as on the date.