Solved

Turn a string into an expression.

Posted on 2001-08-23
11
317 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

790 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