Challenging DLookup expression

[Tom]

I have a table that includes 2 fields: Currency and Exchange Rates (as shown
below):

Currency ExchangeRate
GBP_USD 1.81
GBP_EUR 1.49
NOK_USD 0.14
NOK_EUR 0.11

Now, in a query (linked to another another table), I pull the a value (field
"BaseCurrency") which might give me an output of "GBP" (Great Britain
Pound).

Based on the GBP value (BaseCurrency), I want to use 2 DLookups in the
query. The first one should give me "1.81" (the ExchangeRate of GBP to
USD); the 2nd one should give me "1.49" (the ExchangeRate of GBP to Euros).

How can I create the 2 DLookups that will give me both exchange rates (based
on the same BaseCurrency value)?

Thanks,
Tom

 

[MGFoster]

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

Your table is not Normalized: you have 2 data items in one cell -
Currency column. You should have 2 columns FromCurrency and ToCurrency.
E.g.:

FromCurrency ToCurrency ExchangeRate
GBP USD 1.81
GBP EUR 1.49
NOK USD 0.14
NOK EUR 0.11

Then the DLookup would look like this:

DLookup("ExchangeRate", "<table name>", _
"FromCurrency='GBP' AND ToCurrency='USD'")

If you want to have the BaseCurrency value in another table, then the
DLookup is more complicated & it would be better to use an SQL query.
Use a recordset to get the result:

SELECT ExchangeRate
FROM <table name> As E INNER JOIN BaseTable AS B
ON E.FromCurrency = B.BaseCurrency
WHERE E.FromCurrency='GBP' AND E.ToCurrency='USD'

If you really want to use your set up the DLookup would look like this:

DLookup("ExchangeRate", "<table name>", _
"Left(Currency,3)='GBP' AND Right(Currency,3)='USD'")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQlCNv4echKqOuFEgEQJlgACg2FZ6wSU6jvvMuFdiPzUVdipPhLMAoL+y
poLxWd0q+9HQTLY0Qo98T/gM
=xy9Q
-----END PGP SIGNATURE-----