I have an Access database in which I need to store some formulas. I have two tables; tblResults and tblTests. The relationship between the two is that some combination of Tests make up a result. For example:
Result1 = Test3
Result2 = Test1/Test4
Result3 = Test2+Test3
Result4 = (Test2+Test1)/Test3
For practical purposes, the maximum number of tests for a result will be four, although if it could be unlimited, that would be OK too. The only operators are the big four and parentheses. The users need to be able to create new Results, new Tests, and define the formula that ties them together.
What would be really cool is if I could invoke the Expression Builder and limit it to tblTests and the math operators I want. I don’t think I can do that, though. At least I haven’t figured out a way.
Another thought is to store the formula as a string. The user would enter a string and the program would validate it. Every time the result is computed, the string is parsed and ‘field names’ are replaced with values. This would be the easiest to program (maybe) but it would be slow and prone to errors in the user entry.
I could tokenize everything, which is essentially creating my own stripped down Expression Builder and store it as
It would be easier to control what was a field and what was an operator. I suppose if I stored it as a string, my parsing procedure would essentially be doing this, so I might as well store it this way anyway.
Then I have to be able to evaluate it, which seems tough. One idea I had was to create a new Excel worksheet, name some ranges the same names as the tests, insert some values into those ranges, then paste in the formula string and read the result. So I’d name three cell Test1, Test2, and Test3, put values in each of those cells, and paste
=(Test2+Test1)/Test3 into an empty cell. The result of that cell would be my return value. I’d have to make sure that the field names were valid Excel names, but that shouldn’t be too hard.
The Excel route seems like overkill, but I don’t really want to create my own expression engine either. Am I missing something obvious here? How would you approach it?