Introduction.
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 on 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, and 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 TextBox to enter the Expression, a Command Button, and a 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 workhorse of our Custom Calculator. With this function we don't have to worry much about the calculation rules, like evaluating expressions in parenthesis first, exponentiation next, multiplication and division next (from left to right, if more than one operator), doing 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.
EVAL() Function Usage
Want to try a 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 the 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)
EVAL() the Expression Parser
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 TextBox. The Eval() Function parses the expression and gives the result. 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 a lot more than simply evaluate 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 the VBA Window.
The sample Demo Project.
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 of 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, the percentage of down payment, repayment schedule with the applicable interest rate. The Salesman has been given the freedom of allowing a discount up to a maximum of 20% on the Retail Price.
An image of the Form he uses to run these calculations is given below:
This is a stand-alone Form (which 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.
The VBA Code
We have a small VBA routine to accept the Text Box Names in square brackets [], to pick their value and replace them 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 the 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
Note: There is no validation check included in the Code to detect misspelled names or unbalanced parentheses etc. These shortcomings will automatically generate an error when the EVAL() function executes. The user will be able to review the expression, make corrections, and re-try.
Download
You can implement this program on any Form with a 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.
CustomCalc.zip
