Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

EVAL() Function Usage

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)

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 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.

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 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.

The VBA Code

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
         strout = strout & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
         strin = strin & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
      End If
     loc3 = loc2
     loc2 = 0
   End If

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)

Exit Sub

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 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 Demo CustomCalc.zip



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

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

  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 [...]

  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. [...]

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


Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


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

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...


Blog Archive

Recent Posts