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