Create Custom Expression Builder / Calculation Engine

Create Custom Expression Builder / Calculation Engine

Post by Nigel » Sun, 21 Aug 2005 14:44:03

My problem is best explained by way of tables and examples...

I am using Access 2000 (in case this is relevant) and I have a table
with the following fields...

Field names
Code Description Formula Value
FIN01 Dept A - Hours Worked Null 40
FIN02 Dept B - Hours Worked Null 30
FIN03 All Departments Total [FIN01]+[FIN02]
FIN04 All Departments Average Avg([FIN01]+[FIN02])

And that is reall it...
There are two problems...

The initial problem - getting a calculated value into FIN03, I can see
that I can interrogate the Formula and reading the string search for
"[field]" and do a lookup to get the value and replace the fieldname
with the returned value eg - "40+30" - I am confused about how I add
the string together - do I have to place a val() around each field -
eg., "val(40)+val(30)" and let access work this out - or is there an
easier/better way?

The second issue is to enable the user to create functions - Live Avg -
I guess that I need to pass this string to a formula function that will
extract the string into its parts - like above - and eventually provide
a string - eg., "Avg(val(40)+val(30))" and then create individual
functions to get the appropriate result?

Again Is there a better way?

Finally - to enable the user to enter a formula - I need to create a
data entry form (very similar to the expression builder) so that they
can enter the appropriate formula - does anyone know of web link to a
code example for what I am trying to do...

All thoughts appreciated... this is keeping me awake at night - which
after 5 days is not good!....


Create Custom Expression Builder / Calculation Engine

Post by Ed Robicha » Sun, 21 Aug 2005 22:48:58

It's generally accepted practice that you NOT store calculated values.
Doing so can and will cause problems; you can display the calculated
results when needed with no performance penalty and no worries about
refreshing old underlying values.

Unless you want your users to create 50+ different expressions (and expect
them to understand how to do so and get it correct), why not have a form
with 2-10? predefined expressions, and let the user select a specific one by
radio button selection. List the expressions on the form by their English
description, add an unbound "results" textbox, then use a command button or
the AfterUpdate event of the radio button group to run select case code to
execute which expression to evaluate and display in the results control.


Create Custom Expression Builder / Calculation Engine

Post by XMVP » Mon, 22 Aug 2005 07:41:51

Try Microsoft Excel.

Create Custom Expression Builder / Calculation Engine

Post by Nigel » Tue, 23 Aug 2005 15:59:22

To answer the above 2 posts... I agree, the reason for this
'calculation engine' is to give the user the flexability so that I do
not need to hard code calculations for the user.
Regarding the 'calculations' available - yes it will only be predefined
as it enables parse the string and perform the relevant calculation

To answer the other question - the user currently uses excel and has
not control over validation and how the users enter information into
the appropriate this is not really viable...

So I am still interested in reading anyone elses views...