Solved

Please help withIf-Then-Else syntax in an Eval Function

Posted on 2009-03-31
10
538 Views
Last Modified: 2013-12-26
I am trying to find the syntax for a simple If-Then-Else comparison in VBScript as evaluated by the Eval.

I have added a reference to the VBScript control in VB.Net and can successfully evaluate a mathematical formula. The comparison formula that works in Access fails with a Type mismatch: 'IIF'. I have tried other syntax that looks like an actual VB If Block and several other structures. These have all failed with a Syntax Error.  

Please provide a sample syntax.

Thanks,

This code works in Access

--------------------------------------------------------------------------------------------------------

Private Sub TestEval()

Dim A As Integer

Dim b As Integer

Dim formula As String

A = 5

b = 2

formula = "IIF((" & A & " > 6), (" & b & "* 5),(" & b & " * 6))"

ANSWER = Eval(formula)

End Sub
 

************************************************************************************

VB.Net Code

---------------------------------------------------------------------------------------------------------

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        sc.Language = "VBScript"

        Dim res As Double = 0

        Dim A As Integer = 5

        Dim b As Integer = 2

        Dim formula As String
 

        formula = "IIF((" & A & " > 6), (" & b & "* 5),(" & b & " * 6))"

        res = sc.Eval(formula)

        '*** Other attempts -- > create a syntax error on the execution of the Eval

        'formula = "IF((" & A & " > 6) Then (res = " & b & "* 5) Else (res = " & b & " * 6)) End If"

        'formula = "IF((" & A & " > 6) Then (res = " & b & "* 5) Else (res = " & b & " * 6))"

        'formula = "IF((" & A & " > 6) Then (" & b & "* 5) Else (" & b & " * 6))"

        'formula = "IF((" & A & " > 6) {res = (" & b & "* 5)} Else {res = (" & b & " * 6))}"

        '$formula  =" if (\$qty<5) {\$fee=$qty *25;} elseif (\$qty<10) {\$fee =100+(20*(\$qty-4));} elseif (\$qty<20) {\$fee=200+(18*\$qty-9));} else {\$fee=380+(15*(\$qty-19));}";

        'expression = " if (\qty<5) {\res=qty *2;} else {\res = 2;}"

    End Sub

Open in new window

0
Comment
Question by:BEDMDunphy
  • 6
  • 4
10 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 24030926
IIF() function is not in vbscript...you can use something like this to add your own...

function iif(psdStr, trueStr, falseStr)
  if psdStr then
    iif = trueStr
  else
    iif = falseStr
  end if
end function

ref: http://www.4guysfromrolla.com/webtech/tips/t041301-1.shtml
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 24030955
If you're doing this is vb.net tho, why add ref to vbscript?  vb.net has a valid iif function...?
0
 

Author Comment

by:BEDMDunphy
ID: 24031447
The ref to vbscript is just to provide the EVAL function which is not included in VB.Net. (I found the instructions on how to do this in an existing question)

The key to what I am doing is to find the syntax for an if block that executes from within the EVAL string parameter. (It does not need to be IIF)
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 24033463
Presumably you've seen this: http://www.devx.com/vb2themax/Tip/18773
In order to incorporate both functions, you would use something like this:

answer would be 12 in this example.

        sc.Language = "VBScript"

        Dim A As Integer = 5

        Dim b As Integer = 2

        Dim answer As Integer = IIf(A > 6, sc.Eval(b * 5), b * 6)

Open in new window

0
 

Author Comment

by:BEDMDunphy
ID: 24038494
Thanks for sticking in there with me.

The actual code I want to provide looks more like this. There is no If block in the code. The formula is coming from a textbox (or a data record in the end) I need to find the right syntax to put in the text box so the EVAL wont fail.

-----------------------------------------------------
  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As        System.EventArgs) Handles Button1.Click
        Dim sc As New MSScriptControl.ScriptControl
        sc.Language = "VBScript"
        Dim res As Double = 0

        res = sc.Eval(Me.TextBox1.Text)

   End Sub
-----------------------------------------------------

The formula in the text box may be:

If RtRate > DaRate then res = RtRate else res = DaRate

or:

If TimeOfDay > 12 then res = DaRate else res = RtRate

RtRate, DaRate and TimeOfDay are known variables

(The only reason the IIF& format came into play is because I tested that in Accsee and it works.)
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 67

Expert Comment

by:sirbounty
ID: 24038508
Ah...I gotcha.
Let me try out some test code here...
0
 
LVL 67

Assisted Solution

by:sirbounty
sirbounty earned 100 total points
ID: 24039057
Coming up empty - found a few great functions online, but they only accept literals...
Perhaps put out a call for more help - there's a request attention link above that will generate more notice to this thread...good luck!
0
 

Author Comment

by:BEDMDunphy
ID: 24058533
Still looking for the correct syntax.

Is there a source of documentation of allowable syntax in the EVAL function as provided by the Microsoft Script Control in the COM tab for VB.Net?  

Is there a way to see more detail on the syntax error on execution other than View Details?
0
 

Accepted Solution

by:
BEDMDunphy earned 0 total points
ID: 24090870
This is a constant problem with Microsoft programming documentation. Somewhere a programmer wrote the script control and provided the error checking for the syntax allowed in the EVAL function. That person knows what syntax is allowed. (but they aint tellin')

This is a small application that will only be used in house. I sent the If statement out to Excel, let Excel calculate it and retrieved the result. It is not pretty but it works.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 24091001
Sometimes getting a bigger hammer is the best way to keep your sanity - glad you found a solution of sorts.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

19 Experts available now in Live!

Get 1:1 Help Now