FEATURED LINKS
Custom Calculator and Eval Function
When we think of a Calculator the image that comes into our mind is the traditional type with button labels 0-9 and with Arithmetic Operators. We have it in Windows Accessories Menu that can be converted into a Scientific Calculator as well. So we don't need to build that again in MS-Access.
We are considering another type of Calculator that can accept a User Defined Expression (consisting of Data Field Names, Constants, Arithmetic Operators, Parenthesis to alter the Order of Calculations), evaluate that expression and provide a quick result.
We don't need to embark on a fancy design task for this Calculator. All we need is a Text Box to enter the Expression, a Command Button and few lines of Code. The result of the calculation can be displayed either in MsgBox or in another Text Box.
Before we start working on the Calculator Project, let us take a look at one of the built-in functions EVAL(), which is the main work-horse of our Custom Calculator. With this Function we don't have to worry much about the calculation rules, like evaluate expressions in parenthesis first, exponentiation next, multiplication and division next (from left to right, if more than one operator), do addition and subtractions last etc. All you need to do is to give the Expression in String Form into EVAL() Function and get the result as output, as simple as that.
Want to try few examples quickly to get a feel of it? Open the VBA Module Window (Alt+F11) and open the Debug Window (Ctrl+G). Type the following expressions in Debug Window and press Enter Key:
? EVAL(“2+3*4/2”)
Result: 8
? EVAL(“(2+3)*4/2”)
Result: 10
? EVAL("log(SQR(625))/log(10)")
Result: 1.39794000867204
? Eval("10^" & "Eval('log(Sqr(625))/log(10)')")
Result: 25
? EVAL("Datediff('yyyy',Forms!Employees!BirthDate,date())")
Result: 45 (the Employees Form must be open)
What we can see from the above examples is that you can write an expression using built-in Functions, Data Field References and Numeric Constants in a Text Box on a Form and submit it to Eval() Function, it can give you the result of that Expression. It gives the User the ability to define an expression and do calculations with the use of Data Field values on the Form.
Tip: The Eval() Function can do lot more than simply evaluating the formula, it can run another Function, trigger the Click Event of a Command Button or Run a Macro. For more details and examples of Eval() Function search Microsoft Access Help in VBA Window.
I have created a sample Database (you can download it from the bottom of this Post) for an Auto Dealer who extends Credits to his Customers with a nominal charge of interest rate 6%, 7% or 8% repayable in 24, 36 or 60 installments slabs respectively. The Salesman has been instructed to deal with the Customers suitably to promote Vehicle Sales. The Salesman has to decide which slab of repayment is appropriate for a particular customer, percentage of down payment, repayment schedule with applicable interest rate. The Salesman is given the freedom of allowing a discount up to a maximum of 20% on Retail Price.
An image of the Form he uses to run these calculations is given below:
This is a stand alone Form (can be linked to Table) with Unbound Text Boxes. The Text Box names are given as Labels to the left. While writing the Expression it is a must to qualify the control Names correctly like Forms!CustomCalculator!Balance. It will not accept references like Me!Balance. It is difficult to use these lengthy names to build the expression when several Field names are involved in the Formula.
We have a small VBA routine to accept the Text Box Names in square brackets [ ], to pick their values and replace it in the expression before submitting it to EVAL() function.
A Combo Box with Arithmetic Operators and Text Box Names in square brackets is also provided on the Screen for easy entry of expressions. The colored control below displays (displayed by the routine for information only) the replaced value of Text Boxes in the expression before submitting it to the EVAL() Function.
When the Calculate Command Button is clicked the result of the calculation is displayed in the Text Box with dark background and the label Result. The VBA Code is given below:
There is no validation check included in the Code to detect misspelled names or unbalanced parenthesis etc. These short comings will automatically generate an error when the EVAL() function executes. The user will be able to review the expression, make corrections and re-try.
You can implement this program on any Form with small change in str = Me![Expression] and Me![result] = Eval(strout) lines in the Code, if different names are used. Customize the Combo Box contents based on your input Field Names.
Download - File: CustomCalc.zip (Size:21.8K)
Lost Links of External Tables
Link External Tables with VBA
Source Connect Str Property and ODBC
Access Live Data in Excel-2
MS-Access Live Data in Excel
We are considering another type of Calculator that can accept a User Defined Expression (consisting of Data Field Names, Constants, Arithmetic Operators, Parenthesis to alter the Order of Calculations), evaluate that expression and provide a quick result.
We don't need to embark on a fancy design task for this Calculator. All we need is a Text Box to enter the Expression, a Command Button and few lines of Code. The result of the calculation can be displayed either in MsgBox or in another Text Box.
Before we start working on the Calculator Project, let us take a look at one of the built-in functions EVAL(), which is the main work-horse of our Custom Calculator. With this Function we don't have to worry much about the calculation rules, like evaluate expressions in parenthesis first, exponentiation next, multiplication and division next (from left to right, if more than one operator), do addition and subtractions last etc. All you need to do is to give the Expression in String Form into EVAL() Function and get the result as output, as simple as that.
Want to try few examples quickly to get a feel of it? Open the VBA Module Window (Alt+F11) and open the Debug Window (Ctrl+G). Type the following expressions in Debug Window and press Enter Key:
? EVAL(“2+3*4/2”)
Result: 8
? EVAL(“(2+3)*4/2”)
Result: 10
? EVAL("log(SQR(625))/log(10)")
Result: 1.39794000867204
? Eval("10^" & "Eval('log(Sqr(625))/log(10)')")
Result: 25
? EVAL("Datediff('yyyy',Forms!Employees!BirthDate,date())")
Result: 45 (the Employees Form must be open)
What we can see from the above examples is that you can write an expression using built-in Functions, Data Field References and Numeric Constants in a Text Box on a Form and submit it to Eval() Function, it can give you the result of that Expression. It gives the User the ability to define an expression and do calculations with the use of Data Field values on the Form.
Tip: The Eval() Function can do lot more than simply evaluating the formula, it can run another Function, trigger the Click Event of a Command Button or Run a Macro. For more details and examples of Eval() Function search Microsoft Access Help in VBA Window.
I have created a sample Database (you can download it from the bottom of this Post) for an Auto Dealer who extends Credits to his Customers with a nominal charge of interest rate 6%, 7% or 8% repayable in 24, 36 or 60 installments slabs respectively. The Salesman has been instructed to deal with the Customers suitably to promote Vehicle Sales. The Salesman has to decide which slab of repayment is appropriate for a particular customer, percentage of down payment, repayment schedule with applicable interest rate. The Salesman is given the freedom of allowing a discount up to a maximum of 20% on Retail Price.
An image of the Form he uses to run these calculations is given below:
This is a stand alone Form (can be linked to Table) with Unbound Text Boxes. The Text Box names are given as Labels to the left. While writing the Expression it is a must to qualify the control Names correctly like Forms!CustomCalculator!Balance. It will not accept references like Me!Balance. It is difficult to use these lengthy names to build the expression when several Field names are involved in the Formula.
We have a small VBA routine to accept the Text Box Names in square brackets [ ], to pick their values and replace it in the expression before submitting it to EVAL() function.
A Combo Box with Arithmetic Operators and Text Box Names in square brackets is also provided on the Screen for easy entry of expressions. The colored control below displays (displayed by the routine for information only) the replaced value of Text Boxes in the expression before submitting it to the EVAL() Function.
When the Calculate Command Button is clicked the result of the calculation is displayed in the Text Box with dark background and the label Result. The VBA Code is given below:
Private Sub cmdCalc_Click()
'-----------------------------------------------------------
'Author : a.p.r. pillai
'Date : November, 2008
'URL : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim str As String, loc1 As Integer, chk As Integer
Dim strout As String, loc2 As Integer, loc3 As Integer
Dim strin As String
Const sqrleft As String = "["
Const sqrright As String = "] "
On Error GoTo cmdCalc_Click_Err
'change the reference if different
str = Me![Expression]
loc1 = InStr(1, str, sqrleft)
If loc1 > 0 Then
strin = Left(str, loc1 - 1)
strout = Left(str, loc1 - 1)
loc2 = InStr(loc1, str, sqrright)
End If
Do While loc2 > 0
strin = strin & Mid(str, loc1, (loc2 - loc1) + 1)
strout = strout & Me(Mid(str, loc1, (loc2 - loc1) + 1))
loc1 = InStr(loc2 + 1, str, sqrleft)
If loc1 > 0 Then
loc2 = InStr(loc1, str, sqrright)
If loc2 = 0 Then
MsgBox "Errors in Expression, correct and retry. "
Exit Sub
Else
strout = strout & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
strin = strin & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
End If
Else
loc3 = loc2
loc2 = 0
End If
Loop
If Len(str) > loc3 Then
strout = strout & Mid(str, loc3 + 1)
End If
'this line can be removed if not required
Me![parsed] = strout
'change the reference, if different
Me![result] = Eval(strout)
cmdCalc_Click_Exit:
Exit Sub
cmdCalc_Click_Err:
MsgBox Err.Description, , "cmdCalc_Click()"
Resume cmdCalc_Click_Exit
End Sub
Private Sub cmdReset_Click()
Me![Expression] = Null
Me![parsed] = Null
End Sub
There is no validation check included in the Code to detect misspelled names or unbalanced parenthesis etc. These short comings will automatically generate an error when the EVAL() function executes. The user will be able to review the expression, make corrections and re-try.
You can implement this program on any Form with small change in str = Me![Expression] and Me![result] = Eval(strout) lines in the Code, if different names are used. Customize the Combo Box contents based on your input Field Names.
Download - File: CustomCalc.zip (Size:21.8K)Lost Links of External Tables
Link External Tables with VBA
Source Connect Str Property and ODBC
Access Live Data in Excel-2
MS-Access Live Data in Excel
Labels: msaccess forms
posted by a.p.r. pillai at 10:21 AM
0 comments
links to this post
