A TextBox Calculator for Access

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the result into the field.

In this short article, I show how to experience the same comfort in any text box, even when displayed in datasheet view. This trick is probably not suited for large scale or commercial applications, but it is rather fun, and perhaps even useful.

Overview

The user types any mathematical expression, such as “312/0.8”, followed by the equal sign. The expression is replaced by its value, 390, which is stored in the field. Unlike in a spreadsheet, the expression isn't stored for future reference. However, typing the equal sign again restores the text of the last expression, so it can be corrected and re-evaluated.

If the user leaves the text box without pressing the equal sign, the default message is displayed: “The value you entered isn't valid for this field.” If the expression contains invalid syntax or is otherwise not understood, the textbox flashes in red, but no message is displayed. Besides that, the text box behaves absolutely normally, and can be used without ever triggering the built-in calculator.


Installation

The implementation is quite simple:
I have not provided a demo database: it's almost quicker to follow the steps above than to download, virus-check, enable, convert, and explore a database from the web... Also, it's probably more pleasant to try it on the copy of an existing form, and not on a bare two-control demo.

Some useless expressions to test:

The Calculator Function

Of course, I didn't write an entire mathematical expression evaluator. I did that once, many years ago, but it's so much easier to use an existing and well-tested tool! But how can we ask Access to evaluate an arbitrary mathematical expression? While designing, I often switch to the Visual Basic immediate window to perform a calculation. I have also access to control sources, queries, etc. But in run-time mode, how to evaluate an expression?

Luckily, there is Eval(). This function is specific to Access and expects the syntax used in control sources and query expressions. It accepts any string containing an expression, and returns the result of its evaluation, exactly what is needed here. A similar function exists for Excel, expecting spreadsheet cell syntax, but neither is part of Visual Basic per se.
The idea is thus quite simple: when the user presses the equal sign, everything typed before is passed to Eval(), and the number returned is written into the text box. Without error handling and the recall feature, this would be a one-liner!

Function Calculator()
                      '
                      ' Author: Markus G Fischer, Geneva, April 2011
                      '
                      ' Purpose: Allow simple calculations to be evaluated in a numeric text box.
                      ' For example, the user types "2^2*3^3=" and "108" is inserted as value.
                      '
                      ' This is a perfect job for Eval(), which calls Jet's expression evaluator,
                      ' so that any expression understood in a query or as control source of a control
                      ' will be accepted. The trigger is the "=" sign at the end of the text; when
                      ' typed alone it will recall the last valid expression.
                      '
                      ' Error management is minimal, a brief flash of red...
                      '
                      ' Usage: enter "=Calculator()" as change event handler of any text box.
                      '
                          Static sstrRecall As String
                          Dim strExpr As String
                          Dim dblResult As Double
                          Dim txt As TextBox
                          Dim i As Integer
                          
                      On Error GoTo BadMath
                          
                          Set txt = Screen.ActiveControl
                          If txt.Text = "=" Then
                              If Len(sstrRecall) = 0 Then sstrRecall = "1+2+3+4"
                              txt.Text = sstrRecall
                          ElseIf Right(txt.Text, 1) = "=" Then
                              strExpr = Left(txt.Text, Len(txt.Text) - 1)
                              dblResult = Eval(strExpr)
                              If Not IsNumeric(strExpr) Then sstrRecall = strExpr
                              txt.Text = dblResult ' Round(dblResult, 2)
                              txt.SelLength = 100
                          End If
                          Exit Function
                          
                      BadMath:
                          If txt Is Nothing Then
                              ' called from wrong event?
                          ElseIf Err.Number <> 2113 Then
                              dblResult = txt.BackColor
                              txt.BackColor = &H6666FF
                              For i = i To 10: DoEvents: Next
                              txt.BackColor = dblResult
                              SendKeys "{BACKSPACE}"
                          End If
                          Err.Clear
                          
                      End Function

Select all Open in new window

The code is rather straightforward, with little need for comments.
Again, the core of the function is line 31, the rest is merely cosmetics.

The same function can be called from all the numeric text boxes in your application. This might seem inefficient: the function is called for each and every keystroke during editing, basically waiting for the rare equal sign to do its magic... That is part of the evolution in handling user input, and the same thing happens in text processors (spell-checking, auto-correct, intellisense, etc). Humans are comparatively so slow that hundreds of things can happen between keystrokes without any noticeable delay.

A word of caution

Why shouldn't this feature be implemented in commercial applications? It's the same reason which led to the “sandbox mode”, namely to limit the tools available to “malware” and malevolent hackers. It is similar to SQL Injection in that user input is passed to a somewhat low-level interpreter, without any control. Apparently, this has nothing to do with mathematical expressions, but Eval() understands much more than just numbers. If you have created a test form, please try the following:
In other words, this feature opens a tiny security hole, or at least it will be perceived that way by some customers. It is tiny because only numbers are returned, without explicit error messages, and mainly because you cannot inject any actions in a Jet expression. Still, a tiny hole is all that is needed for an expensive law suit, and you will have difficulties proving that it couldn't have been part of some exploit involving your application.

This being said, only a minuscule number of Access applications are commercial; the vast majority never leave the company or organization where they were developed. In all of those, the built-in calculator will not pose any threat. What's more, if sensitive information is available through lookup functions, e.g. the highest salary in the employees table, then the entire database security needs to be revised. Security based on obfuscation is never a good idea!

The Story behind the Calculator

I was implementing a new pricing model, with a new input form, and found myself doing quite a lot of data entry, for test purposes. However, the figures needed to be realistic, and I was switching between Access and some calculator or spreadsheet to get the numbers right. I thought many times that I would prefer to enter the calculations in-place. At one point, I used Eval() for something else and the two collided: a few minutes later, I had a five-line version of the function presented here.

It became surprisingly useful. Let's see, three times the base price of 187 minus 20% commission... Yes, I can do that in my head, but typing “3*187*.8=” is clearly faster! My numbers were more realistic and the testing was a little more fun.

As it happens, I left the function active in the beta version. It was not meant as a feature, I perceived it as just a design tool. But, as you might have guessed, I was watching a beta-tester entering numbers and when she started scribbling on a scape of paper to get a price minus 16%, I told her about the calculator. The project supervisor was watching this, and he immediately rushed to his computer to try it. He came back saying “did you really add a calculator to all the cells in that form?” — Well...


Anyway, it's now a feature; they would be very disappointed if I were to remove it. But I will leave it as an Easter egg. No documentation, no support, no hint in the interface. The trick will be passed along by users: “Hey, let me show you something cool!”


Markus G Fischer
(°v°)

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.