Solved

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

Posted on 2009-03-31
10
541 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best way to export and then import DHCP to a new server? 6 62
Need an intro to -- .Net SQL Authorization Manager 7 80
Authentication of Web Services 3 54
Help me. 3 48
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

863 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

23 Experts available now in Live!

Get 1:1 Help Now