Evaluating Math Strings

[Rawb]

Herro everyone!

I've got an interesting situation: I need to add some sort of automated "math parser" to my database and was wondering how to handle it. I've seen a couple of parsers out there that do what I'm wanting, but nothing that seems to work with VBA. . .

Below is an example of what I need to be able to parse:
I have 4 fields that I might use:

• Pn - A 4 character string that is used to lookup the formula I need to evaluate.
• Hw - A 3 character field that can be converted into a Height dimension. The first two characters combine to form an integer while the last character is the number of 1/8th's (so an Hw of 175 would be 17 and 5/8ths or 17.625).
• Ww - Identical to Hw except is converted to a Width dimension (instead of Height)
• Ow - Same as the above two, except it will not always be given (if empty, it's evaluated as 0.000) and it will always be less than or equal to Hw.

Assuming the following:

Pn|Hw|Ww|Ow|Hg|Wg
"1674"|"265"|"265"|"170"|8.737|28.250

Also assuming the following equations (stored as a strings):

Pn|He|We
"1674"|"Hw-Ow-2.675"|"Ww-0.625"

I need He and We to evaluate to the following:

He = 6.95 (Hw=26.625, Ow=17.000; 26.625-17.000-2.675=6.95)
We = 26 (Ww=26.625; 26.625-0.625=26)

I only need to be able to handle simple math (+ and -, no multiplication or division). Basically, I just need to know how I can read my strings so that they are actually read as mathematical expressions. Once I know that part, the conversion from Hw, etc. into numbers will be simple.

[ajetrumpet]

Below is an example of what I need to be able to parse:
I have 4 fields that I might use:

• Pn - A 4 character string that is used to lookup the formula I need to evaluate.
• Hw - A 3 character field that can be converted into a Height dimension. The first two characters combine to form an integer while the last character is the number of 1/8th's (so an Hw of 175 would be 17 and 5/8ths or 17.625).
• Ww - Identical to Hw except is converted to a Width dimension (instead of Height)
• Ow - Same as the above two, except it will not always be given (if empty, it's evaluated as 0.000) and it will always be less than or equal to Hw.

Well...to get you started, how about these conversion syntaxes:

PN = ??? NOT SURE WHAT YOU WANT (USE DLOOKUP() THOUGH)

HW = round(clng(left(HW, 2)) + (clng(right(HW, 1)) / 8), 2)

WW = same as HW.

OW = ROUND(NZ(HW FORUMULA HERE, 0), 2)

width and height are numbers regardless of what measurement they are in.

[Rawb]

I'm not concerned with that portion of it. What I'm looking for is a way to evaluate a string as math. For example:

strString = "4+6"
strAnswer = MathEval(strString)

And have strAnswer = 10

[ajetrumpet]

I'm not concerned with that portion of it. What I'm looking for is a way to evaluate a string as math. For example:

strString = "4+6"
strAnswer = MathEval(strString)

And have strAnswer = 10

actually I think you CAN add strings together if they are numerical. but don't quote me on that. test it out.

Your post is a bit unclear actually. if you have a number of pieces of data that are being concated together as strings, the obvious alternative would be to convert each of them using C functions to get from strings to longs (or ints, or anything mathematical that you want) at runtime and then simply use math operators to get an answer.

[Rawb]

actually I think you CAN add strings together if they are numerical. but don't quote me on that. test it out.

Your post is a bit unclear actually. if you have a number of pieces of data that are being concated together as strings, the obvious alternative would be to convert each of them using C functions to get from strings to longs (or ints, or anything mathematical that you want) at runtime and then simply use math operators to get an answer.

Sorry, I tend to get a little long-winded sometimes. . .

I'm not looking to add strings together.

What I have is an equation saved as a string, complete with plus and minus signs. I want to be able to "parse" that string and figure out what the saved equation equals.

For example: I have a string "4+6". I'm looking for a way give that string to Access and have it able to figure out that "Oh hey, that's math!" and then just tell me that the answer is "10".

[weekend00]

PN = ??? NOT SURE WHAT YOU WANT (USE DLOOKUP() THOUGH)

PN is the pointer/index to find the number of HW and so on.

why do you need round()?

[ajetrumpet]

Sorry, I tend to get a little long-winded sometimes. . .

I'm not looking to add strings together.

What I have is an equation saved as a string, complete with plus and minus signs. I want to be able to "parse" that string and figure out what the saved equation equals.

For example: I have a string "4+6". I'm looking for a way give that string to Access and have it able to figure out that "Oh hey, that's math!" and then just tell me that the answer is "10".

the only way I know is with a UDF. For instance:

Option Compare Database
Option Base 1


Function CalcEquation(inputFormula As String) As Long

Dim lastOpPos As Long
Dim elNum As Long
Dim i As Long
Dim arrMath() As Variant

inputFormula = "+" & inputFormula

elNum = 1
lastOpPos = 1

For i = 2 To Len(str)

    If Not IsNumeric(Mid(str, i, 1)) Then

        ReDim Preserve arrMath(elNum)
        
            arrMath(elNum) = Mid(str, lastOpPos, i - lastOpPos)
            lastOpPos = i
            elNum = elNum + 1
    
    End If

Next i

For i = LBound(arrMath) To UBound(arrMath)

    CalcEquation = CLng(CalcEquation) + CLng(arrMath(i))
    
Next i

End Function

This can be condensed even further without the use of the array. What this does though is take the portions out as integers (positives or negatives), converts them to LONGS and adds them together with the + operator, which is basically just doing math. Adding numerical types with the "+" sign results in math calculation, but doing it on strings results in simple concatenation.

[TheShabz]

here's how my C++ brain would do it. start counting your string until you get to + or -. save that as a variable, making note of the operator. skip the + or - then keep going until the end and store that as another variable (adding more if there are multiple operators).
at the end,
if (operator = "+")
varTotal = varNum1 + varNum2
else if (operator = "-")
varTotal = varNum1 - varNum2
else
msgbox "something's wrong here chief. try again"
end if

[ajetrumpet]

here's how my C++ brain would do it. start counting your string until you get to + or -. save that as a variable, making note of the operator. skip the + or - then keep going until the end and store that as another variable (adding more if there are multiple operators).
at the end,
if (operator = "+")
varTotal = varNum1 + varNum2
else if (operator = "-")
varTotal = varNum1 - varNum2
else
msgbox "something's wrong here chief. try again"
end if

Basically what I said, varied about .0004 degrees. Good to know though! And BTW, that code of yours is not C++.

[TheShabz]

Basically what I said, varied about .0004 degrees. Good to know though! And BTW, that code of yours is not C++.

I know the code isnt C++ but the logic is very un-object oriented, which is what I was getting at.

[weekend00]

assume the two table name are table1 and tabl2.

run following query (ValueHe and ValueWe are what you want):

SELECT Table1.Pn, Table1.hw, Table1.ww, Table1.ow, Table2.he, Table2.we, clng(left([HW], 2)) + clng(right([hW], 1)) / 8 as hhw, clng(left([oW], 2)) + clng(right([oW], 1)) / 8 as oow,clng(left([wW], 2)) + clng(right([wW], 1)) / 8 as www,eval(Replace(Replace(Replace([he],"HW",hhw),"wW",www),"oW",oow)) AS ValueHe, eval(Replace(Replace(Replace([we],"HW",hhw),"wW",www),"oW",oow)) AS ValueWe
FROM Table1 INNER JOIN Table2 ON Table1.Pn = Table2.pn;

[Rawb]

OK. . .

Well. . .

That kicked my solution right out of the water.

I WAS working on a Module that would tokenize a string (while preserving the delimiters) and then just cycle through the tokens.

I like your way better though!

P.S.
I was nearly done with my method too lol

[weekend00]

I should have waited until you finish you code then post mine.

[TheShabz]

post it anyway Rawb.

[Rawb]

Yes you should have!

And also, this was a Programming question, not a Queries question!

CHEATER!