Formula or If Statement question

[idehen]

Crystal version - CR10

I am using two tables example Fees and FX/Exchange rates.

Fees Table info:
ID Date1 Amount Currency Fx/rates
1 01/01/2007 20.24 USD 1.120
2 03/05/2007 10.22 EUR 1.090
3 09/01/2008 50.62 EUR 1.250

I have a date parameter the report runs for which is where Date above is less than or equal to parameter date (Date1 <= ParameterDate)

The Fx/rate table contains all the exchange rate values for each day of the month for any given year till end of 31/12/2007.

Problem:
Using the above example, the fx/rates field though coming from the Fees Table is wrong and so need a formula to pull this information from the actual Fx/rate table and the formula should work as:

If Date1 is equal to Parameter Date then Fx/rate (from the FX table) else if Date1 < Parameter Date then i want the Fx/Rate for the most recent date that relates to the currency.

If it helps, the Fx /rate table and the fees table both have the Currency ID in common.

I know this is a long question. Just thought i give as much details as i can as am really stuck with this. Any help most appreciated.

Solomon

 

[Madawc]

Sounds like you need a running total with a formula. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

If that's not it, do a test version that shows intermediate values, see where things are going wrong.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I am 99% sure I have already answered this question, and I wonder why you are posting a new thread on the same topic instead of responding to your initial thread, and why you are posting under a different handle.

-LB

 

[dgillz]

LB,

This could be coursework. I have run into this before.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?