Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-03-31
10
Medium Priority
?
594 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
[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
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

604 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