find appropriate exchange currency rate

[Baybars]

New to access, but to deja.com. Looked through many posts, but can not
find a good post for my problem (I believe there is, but my access
level didn't allow me to understand).
I have a table for cash flow of a shop. Forreign currencies are
accepted. I built the tables accordingly: CURRENCY TYPE (USD, EURO,
POUND ETC), AMOUNT OF THE CURRENCY. RATE FOR LOCAL CURRENCY and LOCAL
CURRENCY VALUE.

When I entered the type of currency (eg USD), I want the table (or
form) to find the correct rate from other table (where I stored the
exchange rates) and show it in appropriate txtbox (then I use that for
the calculation). If it must be through VBA code, I tried
Me!txtLocalValu e = Me!ForCurrency * Me!Rate

If I have a fixed rate, no problem, but since I have variable rates, I
failed.

I hope it is clear enough what I need.

Any help is very much appreciated

Regards,
Baybars

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would seem you need a table like this:

ExchangeRates:
NationalCurrenc y Text(7)
ValueOneUSD Currency
Effective Date

The data would look like this:

NationalCurrenc y ValueOneUSD Effective
JPY 110 2/5/2003
JPY 112 2/8/2003
GBP 0.60 2/3/2003
GBP 0.62 2/7/2003

The ValueOneUSD would be the number of NationalCurrenc ies need to
equal one US dollar. The Effective date would be necessary to tell
what the exchange rate was for past transactions.

To get the exchange rate you'd have to query the ExchangeRates table.
E.g.: We want to know what the current USD value is for 200 JPY.

SELECT 200/ValueOneUSD As CurrentRate
FROM ExchangeRates AS ER
WHERE Effective = (SELECT Max(Effective) FROM ExchangeRates
WHERE Effective <= Date()
AND NationalCurrenc y = ER.NationalCurr ency )
AND NationalCurrenc y = "JPY"

Say the value of "ValueOneUS D" was 110: 200 / 110 = 1.8182 USD

Then you'd have to get the query return value "CurrentRat e" & put it
into a form control.

HTH,

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP8edZoechKq OuFEgEQLSygCgis c4Hf3CuhoR2IkPp ex+ZNz9nv0AoLRp
xuhiCiljzOr3aPb PjN3+wdS2
=ZAX7
-----END PGP SIGNATURE-----

Baybars wrote:

New to access, but to deja.com. Looked through many posts, but can not
find a good post for my problem (I believe there is, but my access
level didn't allow me to understand).
I have a table for cash flow of a shop. Forreign currencies are
accepted. I built the tables accordingly: CURRENCY TYPE (USD, EURO,
POUND ETC), AMOUNT OF THE CURRENCY. RATE FOR LOCAL CURRENCY and LOCAL
CURRENCY VALUE.

When I entered the type of currency (eg USD), I want the table (or
form) to find the correct rate from other table (where I stored the
exchange rates) and show it in appropriate txtbox (then I use that for
the calculation). If it must be through VBA code, I tried
Me!txtLocalValu e = Me!ForCurrency * Me!Rate

If I have a fixed rate, no problem, but since I have variable rates, I
failed.

I hope it is clear enough what I need.

Any help is very much appreciated

Regards,
Baybars

[Mike Sherrill]

On 28 Nov 2003 06:20:04 -0800, ba*********@yah oo.com (Baybars) wrote:

I have a table for cash flow of a shop. Forreign currencies are
accepted. I built the tables accordingly: CURRENCY TYPE (USD, EURO,
POUND ETC), AMOUNT OF THE CURRENCY. RATE FOR LOCAL CURRENCY and LOCAL
CURRENCY VALUE.

When I entered the type of currency (eg USD), I want the table (or
form) to find the correct rate from other table (where I stored the
exchange rates)

Are you sure you want to do that? How stable are your exchange rates?
How stable are they in the worst case?

--
Mike Sherrill
Information Management Systems