Solved

# Turn a string into an expression.

Posted on 2001-08-23
320 Views
Greetings,

I want to take a field out a table which is a text string. For use in a report I want to convert this string into it's elements. I have the expression "200 + 300 - 400" stored as a string. What i want to do for the report is actually do the math. so i want to convert the string to:

myTotal = 200 + 300 - 400

the reason i'm doing this is because i want the report to be flexible enough for the user to evaluate their own expressions. so it might not nessessarily be 3 elements. the expression might be "100 / 5.7". Can someone out there help me out?
0
Question by:joGor
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2
• +3

LVL 3

Expert Comment

ID: 6418090
Hi,

As long as the elements are delimited by spaces, it seems you could have a parser which would take the appropriate action based on the operator (+ - * /).  However, if your statements allow parentheses (to determine order of processing) and you want to do the standard math order (* / before + -, etc.), then some kind of recursive function is what you would want.

What operators do they have besides the +, -, *, and /?  Exponentiation, square roots, logs, etc.?

Ken
0

Author Comment

ID: 6418116
It's just going to be the 4 basic "+ - * /".
0

LVL 2

Expert Comment

ID: 6418192
You might want to consider entering each part separately: num1, oper1, num2 ,oper2, num3, etc.  You could just have a selection for oper and free form entry (restrict entry to numbers or use a VBA check for IsNumeric).  Or you might want to allow the whole thing to be enterred and then the parts parsed.  If the later yo need to allow for more variability.  Someone could enter 5+3, 5 + 3, or even 5  +3.

In either cases, order of operations can impact results.  Is 5 + 3 / 2 interpeted as (5+3)/2 or 5 + (3/2)?  Math rules force one interpretation but the user may expect another.

Jack
0

Author Comment

ID: 6418248
I guess my real question should have been: How do I go about parsing a string in Access?
0

LVL 2

Expert Comment

ID: 6418300
L = 4 'Length to test
If IsNumeric(strLogY) Then

For N = 1 To L
sTest = Mid\$(strLogY, N, 1)
If sTest = "." Then
MsgBox ("No decimal points allowed.  You entered " & strLogY)
GoTo enterlogY:
End If
Next

In this example L is the length of the string and the string is srtLogY.  I am making sure the string does not contain a decimal point with a number.  If so I start them over again (the goto)

You need to look for any of the 4 operators and use what is before and after them as your numbers.  You will probalay want to just eliminate any excess spaces with a TRIM function.

Parsing is not that hard but is very repetive (which is why you use a loop) and usually involves lots of trial and error before you get it right.  Fortunately computers are fast and not easy bored <g>.

Jack

0

LVL 8

Accepted Solution

DennisBorg earned 200 total points
ID: 6418310
joGor:

If you have expressions such as you've described, then you can use the Eval() function in Access.

For example, you can set your ControlSource property of your myTotal control to:

=Eval([MyStringExpressionField])

Of course, replace "MyStringExpressionField" with the real name of your field that contains the expression.

See the Access documentation for further details about the Eval() function.

-Dennis Borg
0

LVL 4

Expert Comment

ID: 6418760
Create a field called Result on the detail section of your report.

In the format event of the detail section place the following code:

On Error Resume Next
Me!Result = Eval(Me!YourFieldName)
If Err.Number <> 0 Then
Me!Result = "Error"
End If
0

LVL 2

Expert Comment

ID: 6419472
The function below will go through a string looking for + and - signs and finding the stuff between as a string.  You will need to modify for other operators you may want to use.  You also probably want to expand it in case where there are more than 2 operators.  Also make sure the captured "numbers" are really numbers with IsNumeric.  I have a test string in the function (strBegin) but you probably need an input or passing from another function.

Jack

Function ParseStr()
Dim strBegin As String
Dim strNum1 As String
Dim strNum2 As String
Dim strNum3 As String
Dim strOp1 As String
Dim strOp2 As String
Dim strTest As String
Dim S As Integer
Dim B As Integer
Dim N As Integer
Dim L As Integer

strBegin = "12 + 312 - 40"
L = Len(strBegin)
S = 0 'counter for number of times thru loop
B = 0  'mark where in string to begin in loop
For N = B + 1 To L
strTest = Mid\$(strBegin, N, 1)
If (strTest = "+" Or strTest = "-") Then
S = S + 1
MsgBox ("Found an operator.  String is " & strBegin & ".  Oper in position " & N)

If S = 1 Then
strOp1 = strTest
strNum1 = Mid\$(strBegin, 1, N - 1)
strNum1 = Trim\$(strNum1)
MsgBox ("First Num is " & strNum1 & " Op is " & strOp1)
End If

If S = 2 Then
'strNum2 = "test"
strNum2 = Mid\$(strBegin, B + 1, N - B - 1)
strNum2 = Trim\$(strNum2)
strOp2 = strTest
MsgBox ("Second Num is " & strNum2 & " Op is " & strOp2)
strNum3 = Mid\$(strBegin, N + 1, L - N - 1)
strNum3 = Trim\$(strNum3)
MsgBox ("Third Num is " & strNum3)
End If
B = N
End If 'strTest
Next

End Function
0

Author Comment

ID: 6421983
Thanks! The Eval() function is exactly what i was looking for. Thanks to everybody else for their help.
0

LVL 8

Expert Comment

ID: 6429230
You're welcome! Glad I could help!

Thank you for the points, and for the 'A'   :-)

-Dennis Borg
0

Expert Comment

ID: 6835935
hello All
Just wanted to let you know that I have figured it out! it was just a few lines of codes but quite a small tricky thing!now I can add the last bit to the query name and update that query!
:)
thanx for the help tho! :)
Regards

Marina
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and installâ€¦
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance andâ€¦
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which â€¦
###### Suggested Courses
Course of the Month2 days, 14 hours left to enroll