Match multiple data from a form to data in a table then calculate

[zdjbel]

Hello,

I have a form with information about a procurement which includes the currency of the purchase and the amount of the purchase in seperate fields, and the currencies vary from order to order. I need a calculated field on the form which will show the Dollar (USD) value of the procurement based on the rate of exchange (ROE) on the date of the order. The rates of exchange are stored in a seperate table. How do I find the matching curreny's ROE and then convert the purchase amount to USD? e.g. on date x the order was placed in currency y in the amount of z - what is the USD equivalent of the order?

The form fields for the search criteria are:
- For date: [MyForm]![DateCreated]
- For currency: [MyForm]![txtCurr]

The form fields for the calculation is:


-For amount: [MyForm]![txtTotal]

The table which contains the rate of exchange information is tROE and has the following fields:
-RoeID
-RoeDate
-USD
-RSD
-EUR

Thanks very much.

[June7]

So you need the exchange record with the most recent date that is less than or equal to the date of procurement. Try something like:

=DLookup("USD","tROE","RoeDate=#" & DMax("RoeDate","tROE","RoeDate<=#" & Me.DateCreated & "#") & "#") * Me.txtTotal

[zdjbel]

ROEProblem.zip

Hello Super Moderator,

Thank you very much for the response. Unfortunately it did not work, perhaps I wasn't very clear in describing the issue. Let me try again.


E.g. I want the calculated control to look at the form's field with the procurement date and currency, determine the date and the currency, then got to table tROE, find the exchange rate for that same currency on the same date, and then divide the field txtTotal with the found rate of exchange.

I'm attaching a copy of the db, the will probably be a lot clearer when you see it in the db.

Thanks very much!

[June7]

Sorry, I wrote the expression for use in VBA code, not textbox ControlSource. And didn't realize the currency needed to be dynamic. This seems to work:

=[Total]/DLookUp([Curr],"tROE","RoeDate=#" & DMax("RoeDate","tROE","RoeDate<=#" & [DateCreated] & "#") & "#")

One problem with domain aggregate functions in ControlSource expressions is they can be slow. You will see the textboxes populate as you scroll through the form.

[zdjbel]

Hi June 7 (Birthady? Mine too!)

Thanks, the calculation worked. However, the formula always looks for the USD value (=[Total]/DLookUp("USD",") if I understand correctly. I need the formula to do the following:

1. Determine the currency in field txtCurr (USD, RSD or EUR),
2. Determine the date in field txtDateCreated
3. Go to tROE, find the date in field RoeDate that matches the date in the form,
4. Match the currency from txtCurr
5. Divide txtTotal with the number (rate of exchange) of the corresponding currency


Thank you so very much for your with this.

[June7]

Look again at my last post. I changed the expression to dynamically determine which field to return rate from.

[zdjbel]

I'm not sure what I missed but it works like a charm now! Thank you again,

ZDJBEL

[June7]

In the first expression 'USD' fieldname was hardcoded in the DLookup, in the second the literal string 'USD' is replaced with reference to Curr field. As long as the values in this field correspond to fieldnames of the tROE table, this dynamic syntax should work.