Solved

Turn a string into an expression.

Posted on 2001-08-23
11
313 Views
Last Modified: 2012-06-27
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
Comment
Question by:joGor
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 3

Expert Comment

by:kenspencer
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

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

Expert Comment

by:jack49a
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

by:joGor
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

by:jack49a
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Accepted Solution

by:
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

by:srauda
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

by:jack49a
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

by:joGor
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

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

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


-Dennis Borg
0
 

Expert Comment

by:marina5
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now