Solved

Access 2010 Calculated Field

Posted on 2013-01-03
17
1,317 Views
Last Modified: 2013-01-06
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
0
Comment
Question by:Jpoppi
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 167 total points
ID: 38742644
Is SelectedCurrencyType always going to be an integer from 1 to 12?  If so, you could use the Choose Function to give you your Exchange Rate like so:

Choose([SelectedCurrencyType], 0.8, 1.2, etc...)
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 167 total points
ID: 38742648
Without any background info on the exact, entire expression, or how you were using it, ... it is difficult to give you a targeted solution.

One solution would be to convert your "Expression" to a Public function.
Then;
1. This will be available anywhere in your app without having to recreate the expression(each time you needed it).
2. Being a function means that you only have to edit this in one place (instead of everywhere you used the expression)
3. You can add code comments, add validation, ...etc
4. It is more "Portable"

Something roughly like this in a Code Module:

Public Function GetRate (SelectedCurrencyType as byte) as single
If SelectedCurrencyType =1 Then
    GetRate=.08
ElseIf SelectedCurrencyType =2 Then
    GetRate=1.2
....
End If

End function

Now you can use this function in place of your expression

=GetRate([SelectedCurrencyType])
...instead of
=iif([SelectedCurrencyType]=1,0.8,(iif([SelectedCurrencyType]=2,1.2,(iif([Selected.....

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38742660
Also a function may be easier to troubleshoot.
...versus trying to troubleshoot a 9 (or more) level nested IIF expression!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38742680
example:
Database11.mdb
0
 
LVL 1

Author Comment

by:Jpoppi
ID: 38742703
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
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38742737
Here's how I'd do it.
ExchangeRate.mdb
0
 
LVL 1

Author Comment

by:Jpoppi
ID: 38742791
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?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38742821
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Jpoppi
ID: 38742899
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
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38742904
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.
0
 
LVL 1

Author Comment

by:Jpoppi
ID: 38742922
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])

Open in new window


The CurrencyTo field was:
([CurrencyFrom]*[SelectedCurrencyMultiplier])

Open in new window

CurrencyExchange---v1.0.32C---07.mdb
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 166 total points
ID: 38743389
Jpoppi, I don't like your idea about separate code for each exchange type. You should do too many work if anytime you will like to add new currency. You can do it with same code. Look at sample (I do not save data, you can do it with query, running from some button (Confirm Exchange, for example). I've modified your table with exchange rates, Now if you like to add currency, you don't need to change code, add new record to table Currency and  to table with exchange rates.
Images should be placed to the Img folder, created in DB's folder.  Unpack all files to some folder and run. Images on continuous form are explained in my article . I hope subforms will work good on touchscreen.
CurrExch.zip
0
 
LVL 1

Author Comment

by:Jpoppi
ID: 38743504
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38743931
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
0
 
LVL 1

Author Comment

by:Jpoppi
ID: 38746093
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]))

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.)
0
 
LVL 39

Expert Comment

by:als315
ID: 38746342
Look at this sample. Transactions now are saved.
CurrencyExchange---v1.0.32C---Co.accdb
0
 
LVL 1

Author Closing Comment

by:Jpoppi
ID: 38749487
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now