troubleshooting Question

Access 2010 Calculated Field

Avatar of Justin
Justin asked on
Microsoft AccessMicrosoft SQL Server
17 Comments3 Solutions1487 ViewsLast Modified:
Hello Experts,

I built my first access database ever for my workplace which is a currency exchange database, and I had it working well. But, the manager wanted more currency types that we could change, which has broken my database.

Basically, my access database was built on iif statements. Toggle buttons on a form set the selected currency type in number form. From there a calculated field in a table would set the exchange rate (the multiplier), and another calculated field would work out the exchanged amount (currency from * exchange rate). The calculated field for the exchange rate was something like
iif([SelectedCurrencyType]=1,0.8,(iif([SelectedCurrencyType]=2,1.2,(iif([Selected.....
and so forth. This worked well, but i found that once i had over 9 different iif statements, access would tell me that the calculated field was too complex.

I have 12 different transaction types. How can i get around this problem?

I need to pick a button on a form, to set an exchange rate (or multiplier). Access will then carry out the following calculation: Currency from * exchange rate = currency to.

Thanks in advance,

jpoppi
ASKER CERTIFIED SOLUTION
IrogSinta

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros