The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.
Access 2010 Calculated Field
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
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
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]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.=1,0.8,(ii f([Selecte dCurrencyT ype]=2,1.2 ,(iif([Sel ected.....
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
Asked:
Who is Participating?
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([SelectedCurrency
...instead of
=iif([SelectedCurrencyType
;-)
JeffCoachman
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([SelectedCurrency
...instead of
=iif([SelectedCurrencyType
;-)
JeffCoachman
Also a function may be easier to troubleshoot.
...versus trying to troubleshoot a 9 (or more) level nested IIF expression!
...versus trying to troubleshoot a 9 (or more) level nested IIF expression!
example:
Database11.mdb
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
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
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?
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
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
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
The CurrencyTo field was:
The SelectedCurrencyMultiplier
Choose([SelectedCurrencyType],[AUDtoUSD],[USDtoAUD],[AUDtoEUR],[EURtoAUD],[AUDtoCNY],[CNYtoAUD],[AUDtoUSD_R],[USDtoAUD_R],[GBPtoAUD],[JPYtoAUD],[CADtoAUD],[NZDtoAUD])
The CurrencyTo field was:
([CurrencyFrom]*[SelectedCurrencyMultiplier])
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
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
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
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","YourRateTa
Of course, obviously you could have the function do the lookup too:
Public Function GetRate(SelectedCurrencyTy
GetRate = Nz(DLookup("SelectedCurren
'Or if SelectedCurrencyType is a string
'GetRate=Nz(DLookup("Selec
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
<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","YourRateTa
Of course, obviously you could have the function do the lookup too:
Public Function GetRate(SelectedCurrencyTy
GetRate = Nz(DLookup("SelectedCurren
'Or if SelectedCurrencyType is a string
'GetRate=Nz(DLookup("Selec
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
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.)
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]))
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
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.
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
Choose([SelectedCurrencyTy