DLookup and Conversion in Report

[Tom]

I need some help with DLookups and some currency conversion in a report.

Here's what I currently have:

Table1 (linked to form):
===============
- has field "PurchaseAmountCurrentYear" (value e.g. 100.00)
- has field "PurchaseAmountNextYear" (value e.g. 150.00)
- has field "Currency" that stores values such as: USD, EUR, NOK, etc.


Table2 (CurrencyFactorsLookups) -- as show between ****s
=======================================
- has field "CurrencyExchange" that stores values such as: EUR_USD, GBP_USD,
etc.
- has field "ExchangeRate" that stores values such as: 1.22, 1.81, etc.


Report1 (linked to Table1):
=================
- has 3 fields: "PurchaseAmountCurrentYear", "PurchaseAmountNextYear",
"Currency"
- has 2 unbound fields: "PurchaseAmountCurrentYear_in_USD",
"PurchaseAmountNextYear_in_USD"
- has 2 unbound fields: "PurchaseAmountCurrentYear_in_Euro",
"PurchaseAmountNextYear_in_Euro"

*****************************************************************
CurrencyID Date CurrencyExchange ExchangeRate
1 4/1/2005 EUR_USD 1.22
2 4/1/2005 USD_EUR 0.82
3 4/1/2005 GBP_USD 1.81
4 4/1/2005 GBP_EUR 1.49
5 4/1/2005 NOK_USD 0.14
6 4/1/2005 NOK_EUR 0.12
7 4/1/2005 PLN_USD 0.27
8 4/1/2005 PLN_EUR 0.23
*****************************************************************


My Report1 has (1st record):
===================
- record has a "PurchaseAmountCurrentYear" value of "100.00"
- record has a "PurchaseAmountNextYear" value of "150.00"
- record has a "Currency" value of "GBP"


Now, what do I want to do in Report1?
=====================================
1. since currency = "GBP" on 1st record (Table1), find record #3 and #4 in
Table2 (I guess via DLookup).

Note: Finding records #3 & #4 is based on the values "GBP_USD" & "GBP_EUR"
in field "CurrencyExchange"
If the "Currency" had been "PLN" in Table1, I would want to find
records #7 & #8 in Table2

2a. based on the Dlookup and finding #3 in Table2, multiple "100.00" by
"1.81" for unbound field "PurchaseAmountCurrentYear_in_USD"

2b. based on the Dlookup and finding #4 in Table2, multiple "100.00" by
"1.49" for unbound field "PurchaseAmountCurrentYear_in_EUR"

2c. based on the Dlookup and finding #3 in Table2, multiple "150.00" by
"1.81" for unbound field "PurchaseAmountNextYear_in_USD"

2d. based on the Dlookup and finding #4 in Table2, multiple "150.00" by
"1.49" for unbound field "PurchaseAmountNextYear_in_EUR"


My questions:
a. how can I find record #3 & #4 in Table2 via Dlookup (based on "GBP" in
Table1)?
b. is the current structure of Table2 okay? Rather than having 8 records
for each CurrencyExchange, maybe I should have 8 fields for each of the
CurrencyExchange rates. If yes, how would the Dlookup work then? (since I
wouldn't look up records any longer... instead I would look up fields).


Thanks so much in advance,
Tom

 

[Duane Hookom]

I would not use DLookup(). My CurrencyFactorsLookups would have at least
three field
FromCurrency, ToCurrency, ExchangeRate.
This would allow you to create a join to the Currency field in table1.