calculate equation stored in string

[Phil]

I'm an amateur and working on a module that uses values
entered by a user in form to fill in variables in an
equation. Its then solved and the answer output to the
form. The equation changes though based on the user's
selection in a listbox and is stored as a string
variable "E".

The problem I'm having is in the calculation. I can't seem
to get the user input variables to fill in the equation.
Both the equation string and the user's variables have the
same "L" and "W" designation. I thought this would
automatically fill-in-the-blank on the equations. When
that failed, I tried to pass the equation to the EVAL
function, but also failed. The one clue I have is that I
can enter the equation exactly as stored in the table
extracted from and it solves it wonderfully. What am I
missing? My code is shown below. Thanks in advance for
your help.



'on error resume next

Dim L As Single
Dim W As Single
Dim A As Single
Dim E As String

L = Forms!fCalc.tbxLen.Value 'store user specified
length in L
W = Forms!fCalc.tbxWid.Value 'store user specified
girth in W

'Find the equation to use based on users selection in list
box and store in E
E = DLookup("Equation", "tOptions", "Forms!
fCalc.lbxOptions = [OptionsID]")

'Fill in user variables and calculate equation
A = Eval(E)

'Output result to form
Forms!fCalc.lblAMain.Caption = A
Forms!fCalc.lblAShdw.Caption = A

 

[Ken Snell [MVP]]

You could try the following code step to replace the L and W variables in
the string with the actual values (note that this code assumes that you have
spaces on either side of the variables in the text string):

E = Replace(Replace(E, " L ", L, 1, -1, vbBinaryCompare), " W ", W, 1, -1,
vbBinaryCompare)

This code should go right after you do the DLookup to get the E string.

 

[Phil]

Thanks Ken, although is the Replace function new? I am
running Access 97 right now. I input your code and it
won't compile. Also can't find any reference to it in the
help files. I will check my Access 2000 at home, later in
the day. Might there be another solution?

-----Original Message-----
You could try the following code step to replace the L and W variables in
the string with the actual values (note that this code assumes that you have
spaces on either side of the variables in the text string):

E = Replace(Replace(E, " L ", L, 1, -1,

vbBinaryCompare), " W ", W, 1, -1,

 

[Douglas J. Steele]

Replace was introduced in Access 2000.

Take a look at http://support.microsoft.com/?id=109330 for an equivalent
function for Access 97.