How do I sum an IIF function in an Access Report?

[Guest]

Duane,

You are a life saver! Everything works perfectly. Thank you for your
patience and time. I greatly appreciate it.

Ryan

Try set a control source of a text box to:
=Sum(Amount * Rate)

--
Duane Hookom
MS Access MVP
--

Duane,

I figured out how to view the Converted Data in a report for each
guarantee
(record). Now I have come to the same original problem: How can I sum a
Calculated Field (Amount * Rate) in a Report to show a total number?

Ryan

I have 600 records that include the following data for Bank Guarantees:

Country - Name - Bank Name - Currency - Amount - Expiration Date - Type

I want to be able to show the EUR and USD equivalent for each guarantee,
while also keeping all the above information (including the 'Original
Amount'). I suppose I should show this in some sort of Query so that
(getting back to my main question a while ago) I can then show the sum of
the
EUR and USD equivalent for all guarantees in a report.

I am not sure how to go about doing this in a Query or any other option
without the use of a long IIF statement. I need the Query to decifer
what
currency each guarantee is based on and then apply the appropriate rate.

I hope this explains my problem. If not let me know.

Thanks,
Ryan



:

I don't recall you ever providing some sample records that identifies
your
table structure with the relevant/significant fields.
What kind of values are you storing in your table/query and how do you
want
to display them in your report?

--
Duane Hookom
MS Access MVP
--

Duane,

I have created one table as you mentioned. Sorry I misunderstood you
before. I am still stuck on the problem of actually converting my
data.
Please let me know how I can do this.

Thanks!
Ryan

:

I had one conversion/rate table. I did not store a currency name as
a
field
or table name since that would not be good practice.

--
Duane Hookom
MS Access MVP
--

Duane,

I created the three tables you suggested. One for the EUR
conversion,
USD
conversion, the EUR rates and I have included another for USD
Rates
since
the
rates are different for each.

That was the easy part. I am still struggling in how to tie the
information
together to show my results in a query for each of my 600 records.

What are the next steps?

Thanks!
Ryan

:

You still don't have this setup as I suggested. I would use two
currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP


Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including
two
column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates
from
my
Rates
Table and the Original Amounts from my Main Table in order to
produce a
new
amount in either EUR or USD. I then can use this new Query for
the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD
amounts
in
the
New Query.

When I create this New Query, what formula should I include
(and
where)
in
order for the query to recognize each individual Original
Currency
and,
based
on that Original Currency, what amount to multiple/divide by in
order
to
display the converted amount in EUR and USD. I am under the
impression
the
only way this is possible is by an IIF statement because
nothing
else
is
working for me.

The New Query needs to be able to decifer between each Currency
and
then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan



:

You should create a solution that normalizes your table of
rates.
For
instance an unnormalized table would look like (excuse the
horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro
Japanese
Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416
0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP


The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency
Amount],0)
Of course you would need to do this for all currencies and
then
SUM
or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency
in a
query.
I
assume you have a table for rates as well as currency types
and
amounts?
Use
the Query builder if you are new to Access. Add both tables
to
your
builder
and link them on the proper fields. In the top line of the
Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a
currency
type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is
this
true?

Maybe you could provide a sample of significant fields
and a
could
records
as well as how you want something to display in a report?