Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Custom Calculator and Eval Function

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.


Share:

5 comments:

  1. It’s posts like this that keep me coming back and checking this site regularly, thanks for the info!

    ReplyDelete
  2. Amiable dispatch and this post helped me alot in my college assignement. Thank you on your information.

    ReplyDelete
  3. [...] the result can be displayed or stored in another control. Find the following Link for guidance: Custom Calculator and Evan Function. __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn [...]

    ReplyDelete
  4. [...] a look at the usage of EVAL() Function here: Custom Calculator and Eval() Function. The Eval() Function can evaluate the expressions written in string format and return their result. [...]

    ReplyDelete
  5. Very valuable lessons learnt here. Thanks for all your good work.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code