Equivalent of "IN" SQL Keyword in VB for Textbox

  • Hello:

    I have a textbox in the header of my report and I'd like it to determine whether a textbox in the body contains either of two values. Here's something like the code I have in my header textbox:

    =IIf(Right(ReportItems!txtItemType.Value,4)="TEST" Or Right(ReportItems!txtItemType.Value,4)="MPLE","Display a value.","Display a different value.")

    I'm getting an error that says, "The Value expression for the textbox ‘txtHeader’ refers to more than one report item. An expression in a page header or footer can refer to only one report item."

    It's actually not referring to more than one report item as you can see from the code. I'm thinking that if there's a VB equivalent to the SQL "IN" keyword, maybe I can get around this error. So I'd like something like this:

    =IIf(Right(ReportItems!txtItemType.Value,4) IN ("TEST","MPLE"),"Display a value.","Display a different value.")

    Can anyone help?

  • Although I'm sure there might be a better way using either the choose or switch functions you could use nested IIFs Something like...

    IIF( Right(ReportItems!txtItemType.Value,4)="TEST", Display a value, IIF(Right(ReportItems!txtItemType.Value,4)="MPLE", Display a value, Display a different value))

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Hi, Luke:

    Thanks for responding! I tried your suggestion, but unfortunately, it's giving me the same error. Any other ideas?

  • Yes I'm seeing the same issue here... Sorry read it quickly and didn't realize you were using body data in your header.

    You could try putting a hidden textbox on your report and doing your value check there, and then just grab that value from the header perhaps?

    Or you could put 2 text boxes overlapping each other in your header and set the visibility based on the value they find?

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Maybe you could try the VB equivalent of CHARINDEX which is INSTR. Yes, the search is in a single string rather than separate values, but if they are still separated by commas then the net result should be the same.

    The syntax is: InStr([start,]string1,string2[,compare]). It returns the start position of where the searched for value was found in the string. Therefore if it's not in the string it will return 0.

    So the format in your example (excluding a value for [,compare] which is optional) would be similar to:

    =IIF(0=Instr(1, "TEST,MPLE", Right(ReportItems!txtItemType.Value,4)), "not found", "found").

    JR


    John Rogerson
    BI Technical Lead
    Clear Channel International

  • you can add custom VB.NET code, like functions to your report so you can use them in the report;

    for example, go under Reports?ReportProperties and go to Code Tab.

    add these two functions:

    Friend Function varInList(ByVal vVariable As Object, ByVal ParamArray vList() As Object) As Boolean

    Dim x As Integer

    For x = LBound(vList) To UBound(vList)

    If (vList(x) = vVariable) Then

    varInList = True

    Exit Function

    End If

    Next

    varInList = False

    End Function

    Friend Function varInList(ByVal vVariable As String, ByVal StringToSplit As String, ByVal SplitDelimiter As String) As Boolean

    Dim vList() As String = StringToSplit.Split(SplitDelimiter)

    Dim x As Integer

    For x = LBound(vList) To UBound(vList)

    If (vList(x) = vVariable) Then

    varInList = True

    Exit Function

    End If

    Next

    varInList = False

    End Function

    then in your code example, you can usethis format:

    =IIf(VarInList(Right(ReportItems!txtItemType.Value,4),"TEST","MPLE"),"Display a value.","Display a different value.")

    note these examples are case sensitive, you could change the way things get compared for the string version so it was not case sensitive.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply