Awards
- Experts Exchange Approved
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.
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:
- create or open a scratch database;
- switch to the Visual Basic Editor and create a new module;
- paste the function below, compile and save;
- create a new form with a few text boxes with a numeric format;
- type the following in the `on change´ event: =Calculator()
- switch to form view and experiment.
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:
4*atn(1)= — pi
(1+sqr(5))/2= — golden ratio
fv(.05,year(now)-1626,0,-2
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.
____________________
Some technical trivia
The difference between Visual Basic expressions and Jet expressions is minimal, especially for numerical expressions. For example, “2 And 3” is 2 for VB (technically a bitwise and) and -1 or True for Jet (technically a boolean and). For standard operations, this is of course totally anecdotal.
Another way to evaluate an expression in Access is to write a dynamic SQL query and to retrieve its output. For example, “SELECT 2^.5” is a valid query returning the square root of two. Using Eval is simpler, but rigorously equivalent to the query version.
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
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!
The code is rather straightforward, with little need for comments.
- The calling control is obtained from the Screen object. Since `on change´ is used, this method is totally reliable in all cases.
- On line 33 the result is passed without rounding. For currency amounts, it would be better to use the commented function to avoid unwanted (and often invisible) additional decimals.
- SelLength (length of the selected text) is set to a large number so that the result is selected after evaluation.
- In case of an error, the background is set to a shade of red for the duration of ten `do events´; this value can be adjusted.
- The equal sign is also cleared (which makes sense in terms of keyboard interface). Sendkeys is perfectly safe when used `on change´ of a text box.
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:
version= — Access Version number
forms.count= — number of open forms
dcount('*','msysobjects')
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.