Carrying out calculation based on equation in table.

[andrewfly]

I have a form that displays 10 variables. 6 are already defined, and the other 4 are user defined. There is also an equation field in this form that allows the user to input an equation based on these variables.
So, for example, I have fields named kAir, Tmax, JC, JA, CS, AmbT, VA, VB, VC and VD.
The user may want to calculate
(kAir * Tmax*JC)/(1/JA*AmbT).

I would like to have this result carried out by access, and input into the same table.

So far I have a sub that calls the table with the equation and variables. I then have it move through the records, and calculate the given equation for each record. To start, I have just coded in a generic equation to use on each record.

Currently, Access calculates the value of the first record, and inputs that value for EVERY record- not very helpful.
Below is the code i am using.

--------------------------------------------------------------------------
Sub CalcPSA()

Dim dbs As Database
Dim PSATable As Recordset
Dim JA As Double, JC As Double, CA As Double, Cs As Double, AmbT As Double, HSnkT As Double, Va As Double, Vb As Double, Vc As Double, Vd As Double, Ve As Double, Vf As Double
Dim PC As Double
Set dbs = CurrentDB
Set PSATable = dbs.OpenRecordset("PSATable", dbOpenDynaset)
PSATable.MoveFirst

JA = PSATable!JA
JC = PSATable!JC
CA = PSATable!CA
Cs = PSATable!Cs
AmbT = PSATable!AmbT
HSnkT = PSATable!HSnkT
Va = PSATable!Va
Vb = PSATable!Vb
Vc = PSATable!Vc
Vd = PSATable!Vd
Ve = PSATable!Ve
Vf = PSATable!Vf

Do Until PSATable.EOF
PSATable.Edit
PC = Nz(CalcPSAFunction(JA, JC, CA, Cs, AmbT, HSnkT, Va, Vb, Vc, Vd, Ve, Vf), Null)
PSATable![TjPredicted] = PC
PSATable.Update
PC = 0
PSATable.MoveNext

Loop

PSATable.Close
End Sub

The function is as follows.
--------------------------------------------------------------------------
Function CalcPSAFunction(JA As Double, JC As Double, CA As Double, Cs As Double, AmbT As Double, HSnkT As Double, Va As Double, Vb As Double, Vc As Double, Vd As Double, Ve As Double, Vf As Double) As Double

CalcPSAFunction = (JA / JC + Cs / CA) * 10

End Function


In the future I would like the user to write their own equation for each record and have Access evaluate this.

What am I doing wrong, and why is the first record's equation evaluation being copied to every record?

 

[workmad3]

the problem is that you are not changing the values for them when you change records
try

Do Until PSATable.EOF
JA = PSATable!JA
JC = PSATable!JC
CA = PSATable!CA
Cs = PSATable!Cs
AmbT = PSATable!AmbT
HSnkT = PSATable!HSnkT
Va = PSATable!Va
Vb = PSATable!Vb
Vc = PSATable!Vc
Vd = PSATable!Vd
Ve = PSATable!Ve
Vf = PSATable!Vf

PSATable.Edit
PC = Nz(CalcPSAFunction(JA, JC, CA, Cs, AmbT, HSnkT, Va, Vb, Vc, Vd, Ve, Vf), Null)
PSATable![TjPredicted] = PC
PSATable.Update
PC = 0
PSATable.MoveNext

instead (notice i moved the do until to above where you assign values to your variables)

As for you other question. It might be possible to do, but it would not be fun or easy to do it and would require a very complex string parsing routine to pull the operators out of the string

 

[andrewfly]

Workmad,
Fantastic, that was it. I knew I was missing something simple because I was not reassigning variables. In terms of assigning the record-specific equation, I was thinking about this...
Would it be possible to have a form displaying the 10 variables with a field where the user inputs the equation, and a 3rd field using the equation field as its control source?

 

[workmad3]

The problem isnt in storing the equation, the problem is in evaluating it in code afterwards. You would need to do something along the lines of

get first word
if variable name, get variable and assign value to result
While not end of string
get a word
if operator store what type it is
if variable use last operator found to apply to result
end if

it doesnt seem like much, but you need to have a way of recognising the operators, recognising variable names and using whats recognised to get a value. This also has no allowance for badly formed equations and would just die, and precedence just doesnt exist here. Adding in precedence, brakets and such like just make it more and more complex.

Im not saying its impossible, im just giving a realistic view of the difficulty of evaluating in code an equation held in a string

 

[andrewfly]

Workmad...thanks for the honesty, I agree with you- it could turn into a big mess.

The last option I've considered is as follows...there would be 5 or 10 already written equations (known to work fine, with proper syntax). The user would simply select the equation they want to use for the record.

The program takes an electrical parts list from some electrical designers and bounces it against a master parts list. The master parts list contains all the part data. That data is then bounced against a few equations, and the results are put into a table. The data reflects the thermal characteristics of the electrical parts- calculating how hot each part is getting with x amount of power running through it.

Essentially, to make our jobs easier, what I'm trying to do is set something up so the user isnt bound to the generic equation hard-coded into our program. Some larger electrical parts get hotter faster (or slower), etc...and we would like the ability to input our own equation (you already know that) to reflect these differences.

That was just some background in case you care...sorry for the long winded response, the program was written 5 years ago by a group of other people, and in my spare time I have been slowly disecting the code.

Maybe someone out there has a better suggestion for what I am looking to do?
Thanks again for all the help workmad, it is much appreciated.

 

[workmad3]

I'd say go with the 5 or 10 pre defined equations. Ive had the fun of making a string parser to handle equations before, and that was using Grep and yacc which are designed to do that kind of thing, it still wasnt nice.
If you are still set on having a full equation runner, i'd suggest still having several equations premade, and setting that up first, so that there is something there already, in case things go pear shaped.

And im sure if you run across any problems, ppl here will be happy to help.

Hope that sounds alright

 

[andrewfly]

I think thats the plan...now I've just got to figure out how to do this, its been years since I've done some fun VB stuff

 

[workmad3]

select statement perhaps?

if you want some real fun, go program it in C

 

[andrewfly]

hmmm.....C......
I think I'll stick to my VB for now, unless you want to write it in C for me?

 

[andrewfly]

Thanks again for the help!

 

[andrewfly]

I'm going to bug you one more time...for some reason I don't think there needs to be a lot of code behind this.
Can't I just take the string that includes the formula and say

CalcPSAFunction = TjFunction

or something of the sort, where TjFunction is the column that contains the equation?

 

[workmad3]

ummm..... no. Basically because the equation in the column is a string. All the above would do is try to assign a string to a function and would throw up an error.

That is of course, unless microsoft have decided to add in a way for access to know what a string is and program itself(which i dont think they have)

 

[andrewfly]

Duh....and my guess is that error would be a type mismatch.

 

[andrewfly]

In case you are interested...i found this to be some help

http://www.vb-helper.com/howto_evaluate_expressions.html

 

[workmad3]

kinda proves my point.... it would be a long hard slog 2 work that out from scratch. good work on google

im half asleep i hadnt thought of checking 2 see if it had been done b4

 

[andrewfly]

yes, google just saved me A LOT of time...but thanks for the advice, I would have never considered all the parsing that was needed. Thanks again.

 

[workmad3]

np

my computer science degree is coming in useful already... only got 2 more years to go.

it is one of those common problems ppl have with computers though. They think
'i can understand exactly what this is, therefore a computer should be able to easily' and it takes a while for ppl to realise that the 'simple' problems are often the hardest as we dont think about what it takes to actually do them in a step by step fashion.

 

[andrewfly]

See, Im an engineer...notice the differences between and engineer and a computer science guy?

 

[andrewfly]

Alright...I am almost there with the parsing. Ive got everything up and running in the Immediate window in VB, however when I try to run the function in Access, I still get the type mismatch error. The function EvaluateExpression() is looking at my string which would be an equation, for example, JA + JC, but can not evaluate JA + JC because they are strings and not integers. How do I get them recognized as integers, such that they are referencing the specific values in the record; i.e., if JA = 1 and JC = 2, EvaluateExpression() would return 3.

 

[andrewfly]

Does the EvaluateExpression() function even know what those variables are?

I've got

Sub CalcPSA()

Dim dbs As Database
Dim PSATable As Recordset
Dim JA As Double, JC As Double, CA As Double, cs As Double, AmbT As Double, HSnkT As Double, Va As Double, Vb As Double, Vc As Double, Vd As Double, Ve As Double, Vf As Double
Dim PC As Double
Dim TjPredicted As Double, TcPredicted As Double, TcFunction As String, TjFunction As String
Set dbs = CurrentDB
Set PSATable = dbs.OpenRecordset("PSATable", dbOpenDynaset)
PSATable.MoveFirst


Do Until PSATable.EOF
PSATable.Edit

If IsNull(PSATable!JA) Then
PSATable!JA = 0
JA = PSATable!JA
Else
JA = PSATable!JA
End If

If IsNull(PSATable!JC) Then
PSATable!JC = 0
JC = PSATable!JC
Else
JC = PSATable!JC
End If

If IsNull(PSATable!CA) Then
PSATable!CA = 0
CA = PSATable!CA
Else
CA = PSATable!CA
End If

If IsNull(PSATable!cs) Then
PSATable!cs = 0
cs = PSATable!cs
Else
cs = PSATable!cs
End If

If IsNull(PSATable!AmbT) Then
PSATable!AmbT = 0
AmbT = PSATable!AmbT
Else
AmbT = PSATable!AmbT
End If

If IsNull(PSATable!HSnkT) Then
PSATable!HSnkT = 0
HSnkT = PSATable!HSnkT
Else
HSnkT = PSATable!HSnkT
End If

Va = PSATable!Va
Vb = PSATable!Vb
Vc = PSATable!Vc
Vd = PSATable!Vd
Ve = PSATable!Ve
Vf = PSATable!Vf

'If IsNull(PSATable!TjFunction) Then
'PSATable!TjFunction = " "
'Else
EvaluateExpression (PSATable!TjFunction)
PSATable![TjPredicted] = PC
'End If

PSATable.Update
PSATable.MoveNext

Loop

PSATable.Close
End Sub

Really quite simple actually, but I don't know how to get EvaluateExpression(PSATable!TjFunction), if PSATable!TjFunction = JA + JC, to know that JA and JC are really numbers.

Help me, I'm stupid and desperate, and (i think), almost done with this dreaded thing.