Report with more than 1 currency

[salamander]

Hi there,

I have a report which shows certain costs if they meet a certain criteria.

So I have the following layour (for simplicity I will omit the other headers as they have no impact whatsoever on my question)

Ref - Total Costs
Blah - £100
BlahBlah - $200*


In the query
Total Costs is evaluated using an expression multiplying the working days between 2 dates and multiplying by a daily cost - this works fine.

In the report
Each row in the original table has a field Currency. This is linked to another table which has the potential currencies and relevant rate. Since this is UK based, exchange rate for £ is obviously 1 and $ is whatever it is on that day. We then divide the total cost by the exchange rate. We also have another field called "Note" (this will make sense in a minute). This contains * for $. So each row in the report looks like:

="£" & Round([FeesAtRisk]/[ExchangeRate],2) & [Note]
I then want to put a note at the bottom (see the reason for note now) which says "* - Converted at exchange rate of £1 = $" & [ExchangeRate] ".".

This of course will not take out the $ always, nor will hide it if no dollar values exist. (ie no need to have a *)

How does one A) display this info conditional on a $ value existing and B) showing the relevant exchange rate?

I am thinking of doing a bit of VBA and SQL (ie using a select on the query to see if we return any results where we have currency = $, if yes then make the label visible and display the rate, if no then don't show it)

Thoughts?

Thanks

 

[edtab]

In design view, right click on the "Detail" band and choose
"build event". This is where you could "conditionally" format
your report by VBA code. You could do whatever you want here
including turning visibility off and on.

 

[salamander]

In design view, right click on the "Detail" band and choose
"build event". This is where you could "conditionally" format
your report by VBA code. You could do whatever you want here
including turning visibility off and on.

Thanks.

I ended up doing this a "wierd" way.

I added another column to the currency table named Notes. In this, I put * for USD and ** for Euros. In the field for the cost, I then appended this note to the end of the value (eg field looks like ="£" & Round([Costs]/[ExchangeRate],2) & [Note], for example £100*)

I created a function which I called Note. This would take in the currency of each row of the results of the query and return a result like "* - Converted at the rate of £1 = $x USD".

Finally, I added a field in the report footer which had 2 DLookups to look at the notes we created to see if we had USD and EUR existing. If both existed then you would see:

* - Converted at the rate of £1 = $x USD
** - Converted at the rate of £1 = €x EUR

Problems with this

If only EUR was there, then you would have

** - Converted at the rate of £1 = €x EUR (jump straight to **)

Any other better ideas?