ms access vba calculations

I have an access database with a form that performs various calculates in vba. The calculated results are displayed as Currency, Percentages.

I want to know the best way to calculate the sums, display the results and store the results in a table.


For example:

Sum 1 (this calculates a figure to be displayed in £'s)

Dim iBasePrem As Integer
iBasePrem = (Me.txtFees * 0.005)
Me.BasePrem = iBasePrem

At present the form control "Me.BasePrem" format is "Standard" and the calculated result is stored in the table as "Number - Long Integer - 2 dec places"



Sum 2 (this also calculates a figure to be displayed in £'s but uses a different method)

Me.Prem_RiskMan = ((Me.txtFees * Me.Amt_RiskMan * 0.0035) / 100)

The control "Me.Prem_Riskman" format is "Standard" and the calculated result is stored in the table as "Number - Long Integer - 2 dec places"
The control "Me.Amt_Riskman" has no formatting and is stored in the tabe as "Number - Single" (as the user input should be between 0-100 with dec places)



Sum 3 (this calculates a figure to be displayed as a percentage)

Me.BaseDscntRate = (Me.CalcPrem / Me.txtFees)

The form control "Me.BaseDscntRate" format is "Percentage" and is stored in the table as "Number - Single"


My question is, im not sure whether i need to cast the calculations in the vba as Cint, Csng, and how best to display the results on the form and also store the results in the table.

The backend tables at present are access tables but in time will be upsized to SQL.

Many thanks
andrewpiconnectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
First question would be: why do you think you need to store these values in the table?  If they can simply be computed, why not do so either in a query, or in the control source of the controls on the form?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andrewpiconnectAuthor Commented:
they need to be stored for future reference i.e
Me.txtFees is a client turnover amount
Amt_RiskMan will be a % figure of derived income
both of the above figures will be taken from a paper form and copied into the system to calculate a price.
The figure 0.0035 is a rating figure that is actually pulled from another table (which i didnt put in my original question to not over complicate the question). This figure will be changed from time to time so any calculated results will show a different value if this is changed in the future, therefore i need to store the Me.Prem_RiskMan as well
0
Jeffrey CoachmanMIS LiasonCommented:
< This figure will be changed from time to time so any calculated results will show a different value if this is changed in the future,>
Then it sound like you could employ a "RatingFigureHistory" table.
This would set the various figures at certain times (or other criteria, if needed)
You can then use this table in conjunction with a query to always get the correct results.

The point here is that you never want to store data that you can calculate.  Obviously there are exceptions, ...but fyed is correct in bringing this issue up for discussion.

;-)

JeffCoachman
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

andrewpiconnectAuthor Commented:
ok gents point taken...

I will have quite a few fields that calculate premium values based on a rating table.

Looks like i will have to explore how to employ a RatingFigureHistory table.

Can you give me a pointer in the right direction to get me started please?
0
andrewpiconnectAuthor Commented:
also.....

back to my original question...if the following sum is be shown as a percentage and possibly stored as Number - Single - 2 dec places, would you CSng when calculating or leave this to the formatting in the control?

Me.BaseDscntRate = (Me.CalcPrem / Me.txtFees)
Me.BaseDscntRate = Csng(Me.CalcPrem / Me.txtFees)
0
Dale FyeCommented:
I'll give you an abbreviated example from a Petroleum and Gas application of mine.

I have a Tax Rates table that contains the standard tax rates for various products and well catetories.  The structure of that table looks like:

ID - autonumber
State_CD
Prod_CD
Well_Cat - indicates whether the well gets special tax consideration
Tax_Rate
Eff_Date
Thru_Date

In a query, I might use this like:

SELECT P.*, P.Vol * T.Tax_Rate as Taxes
FROM qry_Production as P
LEFT JOIN tbl_Tax_Rates as T
ON P.State_CD = T.State_CD
AND P.Prod_CD = T.Prod_CD
AND P.Well_Cat = T.Well_Cat
AND P.Sales_Date >= T.Eff_Date
AND P.Sales_Date <= NZ(T.Thru_Date, Date())
WHERE P.Sales_Date = #7/1/13#

This query would give me the production data and the tax my client has to pay for each well and product on a given date.  NULLs in the computed [Taxes] column would indicate where there is data missing in one of the key fields in the JOIN.  The caution here is that when a user attempts to add a new rate, you have to make sure that the new record would not overlap the dates of another record (not quite as easy to do as a simple unique index.

The caution here is that when a user attempts to add a new rate, you have to make sure that the new record does not overlap the dates of another record (not quite as easy to do as a simple unique index.

BTW: The non-equi join cannot be duplicated in the Access query grid.  

I have used another technique in the past which only has a [ThruDate] but this requires lots of gyrations to figure out the rate on a particular date, so I find it easier just to include both dates.
0
Jeffrey CoachmanMIS LiasonCommented:
<Can you give me a pointer in the right direction to get me started please? >
It would involve quite a bit of work, but in some cases, .it is worth it.

I have a sample on this, I will try to dig it up tonight...
0
andrewpiconnectAuthor Commented:
boag2000,

Excellent....many thanks
0
Jeffrey CoachmanMIS LiasonCommented:
0
andrewpiconnectAuthor Commented:
oops....got sidetracked.

fyeds...thanks for that, i will go through your example later on tonight and see if i can make use of it..

thanks again
0
Jeffrey CoachmanMIS LiasonCommented:
I lost track of my original sample so here is a quick, simple workup, ...not perfect, .but you can see how it works through the form...
PriceHistory.mdb
0
Dale FyeCommented:
Hope that helps.

As Jeff said, there are valid reasons to save some computed values, but that should be the exception, not the rule.  The advantage of not storing the value is that if any of the values that are used in the computation are changed, and the computed column is not updated, then you will not have the correct value stored.
0
andrewpiconnectAuthor Commented:
thank you both for your valued assistance.

I shall go through both your ideas and theories tonight and decide which way to go.

My original thinking was that because i will 40+ different calculated fields based on 40+ different rates pulled from a rating table, i wanted to break the norm and store the results, so that when the rates are changed they will not effect the previously calculated fields.

My thinking cap on tonight chaps....many thanks
0
Jeffrey CoachmanMIS LiasonCommented:
Yep, we all know that sometimes you just need to "Git er' Done".

So adding in a new table, loading it, then building a function, ...etc, ...might not be an option here at the current time.

Take your time, then get back to us...
;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.