Access 2010 Calculated Field

Also a function may be easier to troubleshoot.
...versus trying to troubleshoot a 9 (or more) level nested IIF expression!

example:
Database11.mdb

Wow. That's Perfect!

Thanks IrogSinta and boag2000! I just tested a choose function and it is working straight off the bat, and a public function will save me rewriting a heap of code later on in the project.

Question: With the public function, can I call on different values from different tables? Instead of having the GetRate values in the module, could it be pulled from a table? for example: I have the SelectedCurrencyType value in table1, and all my rate values in table2.

Public Function GetRate (SelectedCurrencyType as byte) as single
If SelectedCurrencyType = 1 Then
    GetRate=[AUD_Rate]
ElseIf SelectedCurrencyType =2 Then
    GetRate=[USD_Rate]

How do I point to a specific rate in my table using the public function method? The whole reason for asking this, is so anyone using the database can change the rates from a form, without going into the code.

Thanks

Here's how I'd do it.
ExchangeRate.mdb

Thanks IrogSinta,

Your example works great.

My database will eventually be used on a touchscreen computers, so I was hoping to use toggle buttons. Is there any way to connect the tblExchangeRates to toggle buttons, and then use the selected value in the Converted text box calculation?

Here you go:

You just need to add more toggle buttons in the frame.  Be sure their option values are sequential.

The button captions are loaded from the table when the form is opened. This way you can easily change the rates as well as the counties on the toggle buttons by simply editing the table.
ExchangeRate.mdb

Thanks again IrogSinta. Very impressive.

I'm only a beginner with access, but I think I know what I need to do to make my database work better.

I think I need to use part of your solution, but I'm not sure how to apply it. I already have toggle buttons, which are attached to lots of things within my database (reports and forms etc). I currently have dedicated fields within my Transactions table, with default values set as my exchange rates, and the SelectedCurrencyType field has an expression that picks one of these fields according to which toggle button is selected on the form to carry out the calculation.

All I need to do load my buttons up with the values from my new ExchangeRate table (like you did in your example), and depending which button is selected, send that value to my Transaction Table, in the SelectedCurrencyType field. (By doing this my whole database won't need to be redesigned, due to my poor design from the start). I can then get rid of the other dedicated fields, and nothing else should need to be changed (I hope!).

I've studied your example, but I can't work out how to make it work the way I need it to. Any more help you could give me would be greatly appreciated. I've attached the database below; if my explanation above wasn't clear enough...
CurrencyExchange---v1.0.32C---Co.accdb

Sorry, I'm unable to open your database.  I'm using Access 2007.  Would you able to save a copy in 2007?  Or perhaps Jeff or another expert may pop in and be able to view this.

I had to remove the calculated fields in the Transactions table to be able to save it in .mcb format.

The SelectedCurrencyMultiplier field was:

Choose([SelectedCurrencyType],[AUDtoUSD],[USDtoAUD],[AUDtoEUR],[EURtoAUD],[AUDtoCNY],[CNYtoAUD],[AUDtoUSD_R],[USDtoAUD_R],[GBPtoAUD],[JPYtoAUD],[CADtoAUD],[NZDtoAUD])

Select all Open in new window

The CurrencyTo field was:

([CurrencyFrom]*[SelectedCurrencyMultiplier])

Select all Open in new window

CurrencyExchange---v1.0.32C---07.mdb

Thanks als315 for the effort you have gone to. I like the whole flag selection thing. Really cool.
The only reason I had seperate code for each transaction was because I didn't know how to do it any other way. I've been teaching myself from google, and once I got suck, I came here! :D

Sorry for not commenting back sooner...
<Question: With the public function, can I call on different values from different tables? Instead of having the GetRate values in the module, could it be pulled from a table? for example: I have the SelectedCurrencyType value in table1, and all my rate values in table2.>

You can do anything you want.

Your original question was how to simplify the expression.
This what I (and the other Experts) posted.

If you want to, instead, store the "Rates" in a table, then you really don't need a function, ...you can just "Lookup" the rate
Dlookup("Rate","YourRateTable", "CountryCode=" & SelectedCurrencyType)

Of course, obviously you could have the function do the lookup too:

Public Function GetRate(SelectedCurrencyType As Byte) As Single
    GetRate = Nz(DLookup("SelectedCurrencyRate", "tblCurrencies", "SelectedCurrencyType=" & SelectedCurrencyType))
    'Or if SelectedCurrencyType is a string
    'GetRate=Nz(DLookup("SelectedCurrencyRate", "tblCurrencies", "SelectedCurrencyType=" & "'" & SelectedCurrencyType & "'"))
End Function

Here I am not trying to take away anything from the other experts posts.
The great thing about this site is that you get a chance to see all the different ways of approaching the same issue.

As with most things in the world of App Dev, ..., there is no "right or "wrong" way to do something...

;-)


Sample 2 attached



Jeff
Database11.mdb

Thanks again boag2000 for your help with my issues.

Yes I agree that being able to see all the different options for solving a problem is great, and working out which one will suit me is half the fun!

I've been able to get aspects of all the above solutions working in some way shape or form. But I'm still struggling with one problem.

I need to record every transaction and the aspects of it, for customer receipts, reporting and to comply with anti money laundering procedures.

I can now call on my exchange rate table and have the values and calculated data appear on my form in textboxes. But what I can't work out is how to save that textbox data to a field in my Transactions table for the corresponding record.

This is still in line with the posts original question. The reason I had calculated fields in my table in the first place, was so I could record the data (the only way I knew how). Now that I've gotten away from the calculated fields, I still need to save the calculated data.

So: my Text box on my form has the control source of

=[CurrencyFrom]*GetRate(Nz([SelectedCurrencyType]))

Select all Open in new window

This returns the correct data. How do I send that data to the CurrencyTo field in my Transactions table?

I read somewhere that this shouldn't be done, and the data should be generated when required from the other values. This won't fly, since I need to keep hard records. (Plus, if I change the exchange rate, the calculated values will change for previous records.)

Look at this sample. Transactions now are saved.
CurrencyExchange---v1.0.32C---Co.accdb

Thanks experts for all your help.

The choose function was a big help in getting rid of my nested iif statements, and the public function was great for calling up my values.
All the supplied examples were invaluable helping me work out how to improve my database.

All these solutions helped me move away from the calculated table fields I was using.