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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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)
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)
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.
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.
<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...
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...
ASKER
boag2000,
Excellent....many thanks
Excellent....many thanks
andrewpiconnect,
You never responded to fyeds post:
https://www.experts-exchange.com/questions/28243893/ms-access-vba-calculations.html?anchorAnswerId=39506270#a39506270
You never responded to fyeds post:
https://www.experts-exchange.com/questions/28243893/ms-access-vba-calculations.html?anchorAnswerId=39506270#a39506270
ASKER
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
fyeds...thanks for that, i will go through your example later on tonight and see if i can make use of it..
thanks again
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
PriceHistory.mdb
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.
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.
ASKER
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
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
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
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
ASKER
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