Resolving a formula when it is defined in a field 1

[lynns]

Below is a vary simple problem description of what I am trying to accomplish

I have a table with 3 fields in it:
Formula - text
A - double
B - double.

The value of each variables is:

Formula= A * B
A = 2
B = 4


I want to read each record in the table and apply the formula to the numeric values. The formula is not constant. It changes with each record and may have more than one mathematical operator within the formula. For example (A ^ 2) + (B ^ 2).

This is a section of the code that I am trying to run:

While Not rs.EOF
Formula = rs(0)
A = rs(1)
B = rs(2)
Answer = A * B
rs.MoveNext
Wend

I would like to use something like:

Answer = Formula

but when I use the syntax I get Type mismatch error.

If I use this:

Answer = Eval(Formula)

I get an error that reads "Microsoft Access can't find the name 'A' you entered in the expression."

Any ideas on how to make the code resolve the text string FORMULA to apply the mathematical operators?

 

[CajunCenturion]

It's doable, but I've not been able to get the instrinsic Eval function to work here.

What does work is to define as Public properties of the form, variables to hold the values for A and B, and for every variable that may be needed in any one of the formula. You must also include a reference to the Microsoft Script Control 1.0 (or later version).

Public VarA As Double
Public VarB As Double

Private Sub cmdICalc_Click()

Dim lRst_Records        As ADODB.Recordset
Dim lObj_Script         As ScriptControl

Set lObj_Script = New ScriptControl
lObj_Script.language = "vbscript"
lObj_Script.AddObject "mycalcs", Me, True

Set lRst_Records = New ADODB.Recordset
lRst_Records.ActiveConnection = CurrentProject.Connection
lRst_Records.Open "SELECT * from tblTestData", , adOpenForwardOnly, adLockReadOnly
With lRst_Records
   .MoveFirst
   Do While (.EOF() = False)
      VarA = .Fields("FldA")
      VarB = .Fields("FldB")
      MsgBox lObj_Script.Eval(.Fields("Formula"))
      .MoveNext
   Loop
   .Close
End With

lRst_Records.Close
Set lRst_Records = Nothing
Set lObj_Script = Nothing

End Sub

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PHV]

Have you tried something like this ?
Formula = Replace(Replace(rs(0), "A", rs(1)), "B", rs(2))
Answer = Eval(Formula)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya lynns . . . . .

I tested the following with a continuous form. Added a control in the details set to [blue]=Answer[/blue]. It works fine, except the new record line shows [blue]#Error[/blue]. The new record line is functional and works fine. Its just the #Error is so ugly! Havn't figured out how to take it out yet. When I do I'll let ya know.

[blue]Public Function Answer()
   Dim Build As String
   Build = Me![purple][b]FormulaCtlName[/b][/purple]
   
   [green]'Replace all A's[/green]
   Do Until InStr(1, Build, "A") = 0
      Build = Replace(Build, "A", Me!![purple][b]DatA_CtlName[/b][/purple], 1, 1)
   Loop
   
   [green]'Replace all B's[/green]
   Do Until InStr(1, Build, "B") = 0
      Build = Replace(Build, "B", Me!!![purple][b]DatB_CtlName[/b][/purple], 1, 1)
   Loop
   
   Answer = Eval(Build)
   
End Function[/blue]

Remember Our Veterans! Remember Sept 11th!

 

[TheAceMan1]

OK lynns . . . . . Got It!

The code allows you to add as many variables as you like in formula. Just add a Do/Loop section for each. Also the code doesn't check for missing data. Let me know if ya want this included.

[blue]Public Function Answer()
   
   If Not IsNull(Me![purple][b]FormulaCtlName[/b][/purple]) Then
      Dim Build As String
      Build = Me![purple][b]FormulaCtlName[/b][/purple]
      
      [green]'Replace All A's[/green]
      Do Until InStr(1, Build, "A") = 0
         Build = Replace(Build, "A", Me![purple][b]DatA_CtlName[/b][/purple], 1, 1)
      Loop
      
      [green]Replace All B's[/green]
      Do Until InStr(1, Build, "B") = 0
         Build = Replace(Build, "B", Me!!![purple][b]DatB_CtlName[/b][/purple], 1, 1)
      Loop
      
      Answer = Eval(Build)
   End If
   
End Function[/blue]

[blue]Cheers![/blue]

See Ya! . . . . . .