Create Custom Expression Builder / Calculation Engine

[Nigel C]

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!....

Thanks.

[Ed Robichaud]

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.
-Ed

"Nigel C" <ni************@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

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!....

Thanks.

[XMVP]

"Nigel C" <ni************@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Again Is there a better way?

Try Microsoft Excel.

[Nigel C]

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
anyway.

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 spreadsheets...so this is not really viable...

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