Calculate a field in Access, using a formula from a text string

A

ALF

Does anyone know of a way to calculate a field in an Access query using
different formulas entered in a database table, as a text string? I have
gotten around this before by splitting up these equations into their
different variables and storing the values into a table. However, that only
works if the equation format stays the same. I no longer have formulas that
closely resemble each other in format and differ only in values.

Any ideas?
 
J

Jeff Boyce

Could you provide an example or two for clarification?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Dale Fye

You might want to take a look at the Eval( ) function. If you pass it a
string ("3 * 6") it will return 18. If you have variables defined in the
string, you will have to use the Replace function first.

If strExpr = "intA * intB
and intA = 3 and intB = 6
Then ?eval(replace(replace(strExpr, "intA", val(intA")), "intB", val(intB)))
will print 18

The down side of this is that you would have to know which variables are in
each equation, or if you had a standard list of variables, you could just
run it through a function that checks strExpr for any of the variables and
replaces them with their appropriate values.

HTH
Dale
 
Top