Carrying out calculation based on equation in table.

[workmad3]

the EvaluateExpression function(if its just a straight copy) needs a bit of modification i think.

You will need to add in to it the ability to recognize your variable names and when it finds them use the value from the table. Two ways to do this. One is to have a string parser before hand that goes through the string from the table, and replaces all the variable names with their values. THe other is to attempt to modify ther EvaluateExpression function to recognise the names and get the values itself.

The first solution is probably easiest. Heres a possibly outline psudeo code:

Assumption: All parts of the expression have a space between them, i.e. a + b not a+b
Assumption: A 'word' is a series of non space characters of length one or greater

Private Function ValueReplace(Expression as String) As String

Dim result as string
Do While not end of string
    Get next word
    If word is variable name then
         result = result + (Value of variablename)
    Else 
         result = result + word
    End If
Loop

ValueReplace = result

End Function

Hopefully that wont be too hard to implement, and then you can do this

EvaluateExpression (ValueReplace (PSATable!TjFunction))

to get your result

 

[andrewfly]

Workmad...thanks once again...I've tried the second scenario you discussed to no success...your first suggestion is interesting, and it should work. However, I'm having a bit of trouble piecing together your outline code. My experience with string parsing is quite limited.

 

[workmad3]

Ok, Ive attached a file that has some very useful string functions in it.

now, how i'd go about doing this would probably be like

Private Function InsertValues(Expression As String) As String

    Dim noOfWords As Integer
    Dim count As Integer
    Dim result As String
    Dim currentWord As String

    noOfWords = SF_countWords(Expression)
    count = 1
   
    While count <= noOfWords
        currentWord = SF_getWord(Expression, count)
        Select Case currentWord
                   Case "<first variable name>"
                         result = result & <first variable name>
                   Case "<second variable name>"
                         result = result & <second variable name>
                   Case Else
                         result = result & current Word
        End Select
        count = count + 1
    Loop

   InsertValues = result

End Function

Hope that helps with getting it started. It requires fairly strict guidelines as to how the users can put their expression in, but if they stick to them, then it should work alright.

Also, please note that this has not been tested at all, i wrote it directly into here

 

[andrewfly]

Workmad...thanks a million...that should help a lot...i owe you one.

 

[andrewfly]

I suppose the issue that I am having is this...will the parser youve started me off with still leave the mathematical functions in place? ie, the + - * / signs?
Lastly, should the <first variable name> be replaced with the actual source of the variable; ie,

result = result & PSATable!JA

I should nominate you for an access-programmers award

 

[workmad3]

I suppose the issue that I am having is this...will the parser youve started me off with still leave the mathematical functions in place? ie, the + - * / signs?

it should do

Lastly, should the <first variable name> be replaced with the actual source of the variable; ie,

result = result & PSATable!JA

yes, (and replace the "<first variable name>" with just JA or whatever is going 2 be in the stored equation)

I should nominate you for an access-programmers award

heh this was all from doing some string parsing in java in my first year of study. Only using access at the moment because i got a summer project that needs it.

 

[andrewfly]

where are you located btw? UK, US...etc??

 

[workmad3]

UK, in the north east

 

[andrewfly]

im from the us in the northeast, but lived in brussels for the past year...just got back a month or 2 ago

 

[andrewfly]

Well, you can stop helping me now if youd like....i've got everything pieced together such that it seems to me like it should work....however it forever freezes access. this is what i 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


PC = EvaluateExpression(InsertValues(PSATable!TjFunction))
PSATable![TjPredicted] = PC
'End If

PSATable.Update
PSATable.MoveNext

Loop

PSATable.Close
End Sub

and

Function InsertValues(Expression As String) As String

    Dim noOfWords As Integer
    Dim count As Integer
    Dim result As String
    Dim currentWord As String

    noOfWords = SF_countWords(Expression)
    count = 1
   
   Do Until count <= noOfWords
        currentWord = SF_getWord(Expression, count)
        Select Case currentWord
                   Case "JA"
                         result = result & PSATable!JA
                   Case "JC"
                         result = result & PSATable!JC
                   Case "CA"
                         result = result & PSATable!CA
                   Case "CS"
                         result = result & PSATable!cs
                   Case "AmbT"
                         result = result & PSATable!AmbT
                   Case "HsnkT"
                         result = result & PSATable!HSnkT
                   Case "Va"
                         result = result & PSATable!Va
                   Case "Vb"
                         result = result & PSATable!Vb
                   Case "Vc"
                         result = result & PSATable!Vc
                   Case "Vd"
                         result = result & PSATable!Vd
                   Case "Ve"
                         result = result & PSATable!Ve
                   Case "Vf"
                         result = result & PSATable!Vf
                   Case Else
                         result = result & currentWord
        End Select
        count = count + 1
    Loop

   InsertValues = result

End Function

Don't worry, I did add all the needed functions from the text file you sent me.