Hi Everyone.

I am hopeful that you will be able to assist me in coming up with a solution
to a problem I am having...

I have two tables:

(1) An Accrual Table - Contains a "start date", "end date" and "daily
accrual".
(2) A F/X Table - Contains an "effective date", and "f/x rate" which is
updated daily.

My problem is that many of these daily accruals are in a different currency
(i.e AUD/USD) than my domestic currency (CAD), and I need to compute all my
results in CAD. Thus, when users pass in parameters (i.e. a profit start date
and end date), I must compute the total amount accrued over this period in
CAD, while the daily accruals are in foreign currencies.

For example, if the profit start date is 01/01/2005 and the profit end date
is 02/01/2005, then I must sum the daily accrual for EACH day in this range,
using the above ranges in table one. The profit start date may overlap or
intersect a given start and end date for the accrual range (in table one), so
I cannot simply convert the accrual amount at the average prevailing rate
during that period. Rather, I must figure out how many days of that given
amnt of accrual, paying attention to the start and end date of the range,
apply and convert each daily accrual at the prevailing spot rate (F/X rate)
on that day, and then sum them all up.

So, for 01/01/2005 my daily accrual might be 100, and my F/X 1.2, giving me
a CAD accrual of $120, while the following day (with the same accrual) the
F/X rate may have risen to 1.3 (so CAD accrual of $130).

Somehow I need to, given the inputted profit start/end dates, calculate the
daily accrual in CAD, getting the individual spot rates for each day in that
range, and converting the daily accrual at that rate. Clearly I cannot just
use a simple join, as my accrual table has only a range for a given record
(i.e. 12/20/04 - 01/15/05), rather than each individual date.

My thought had been to write a VBA function with the following inputs:
(1) Start Date of Accrual Range
(2) End Date of Accrual Range
(3) Daily Accrual during this range
(4) Profit Start Date
(5) Profit End Date
(6) Table of F/X Rates (for each date - one rate)

This function would compare the start date of the accrual range to the
profit start date, and if profit start accrual start, then start computing
the summed accruals as at the profit start date, otherwise the accrual start
date. For each day in this period, the F/X rates for each given day would be
summed, and then averaged (as this mathematically will give me the same
result as multiplying each accrual by the spot rate on that day), stopping at
either the profit end date or accrual end date (which ever is earlier). The
function would return this rate, which would then be multiplied by the
accrual amount to get the CAD value.

This is very attractive but for one reason: This is to be hosted via the web
(ASP.net), and custom VBA functions CANNOT be called from the web, despite my
best efforts. Thus, I must come up with another way to accomplish this, using
a native access query, but I am out of ideas...

Any help would REALLY be appreciated. I'm sorry for the very long message,
but I wanted to make sure you understood what I was trying to do, as it is
somewhat difficult to explain.

Thank you so very much in advance!

David