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.

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

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


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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

DIRectory and File Copy Utility

Last week we have seen how to use Dir() DOS Command , it’s ability to read files from the Disk  one by one and display it on the Debug Windo...

Labels

Blog Archive

Recent Posts