none
Testing for Null in RDLC Report Field RRS feed

  • Question

  • In .net 2.0, vb.net, I am trying to display a strPhCell phone number char field, from a DataSet,  using the Format() function. The Format works fine, but whenever there is NO Phone Number, the string "#error" displays on the report. I have tried every way I can think of to detect a Null or blank, but nothing works.  Someone on ExpertExchange suggested the following ... which also does not work if there is no phone number.  Can someone help ?

    =IIF (Fields!strPhCell.Value is nothing, nothing, Format(cdec(Fields!strPhCell.Value),"(###) ###-####") )

    cgai

    Monday, October 30, 2006 6:10 AM

Answers

  • IIF is a function call of the VB runtime and therefore the CLR always evaluates all arguments.

    You should rather write a custom code function with an IF-statement and call the custom code function from the expression: =Code.ConvertValue(Fields!strPhCell.Value)
    Then, in the custom code section of the report, add a function similar to this:

    Public Function ConvertValue(ByVal value As Object) As String
        If IsNothing(value) Then
            Return Nothing
        Else
            Return Format(CDec(value),"(###) ###-####") 
       End If
    End Function

     

    -- Robert

     

     

    Thursday, November 9, 2006 7:07 PM
    Moderator
  • This works For Me

    Set the PhoneNumber Value to something like this;

    =Code.ConvertValue(Fields!Phone.Value)

    Put this code in the Custom Code.

     

    Public Function ConvertValue(ByVal value As Object) As String
        If IsNothing(value) or Len(Trim(value))=0 Then
            Return Nothing
        Else
     Return Format(CDec(value),"(###) ###-####")
       End If
    End Function

    Friday, October 8, 2010 2:51 PM

All replies

  • Try this:

    =IIf(IsNothing(Fields!PhCell.Value), "NULL", "NOT NULL")

    Monday, October 30, 2006 4:26 PM
  • Thanks for your response ...

    You suggestion does not work ... if there is no phone number in the field, the string "#error" is printed on the report.

     >> Try this:   =IIf(IsNothing(Fields!PhCell.Value), "NULL", "NOT NULL")

    SteveH

    Tuesday, October 31, 2006 5:37 AM
  • Maybe for my response to make more sense, here is my actual binding string that I tried in the report's TextBox:

    =IIF(IsNothing(Fields!strPhHome.Value),"n/a" , Format( cdec(Fields!strPhHome.Value),"(###) ###-####" ))

    The format works correctly, if a phone number exists, otherwise "#error" is printed.

    Thanks,
    SteveH

     

     

     

     

    Tuesday, October 31, 2006 6:56 PM
  • I dont believe there is a way to check for null. A workaround is if you instead return something like "n/a" from your datasource. Just add isnull(PhHome,"n/a") in your query.

     

    Umer

    Thursday, November 9, 2006 4:39 PM
  • IIF is a function call of the VB runtime and therefore the CLR always evaluates all arguments.

    You should rather write a custom code function with an IF-statement and call the custom code function from the expression: =Code.ConvertValue(Fields!strPhCell.Value)
    Then, in the custom code section of the report, add a function similar to this:

    Public Function ConvertValue(ByVal value As Object) As String
        If IsNothing(value) Then
            Return Nothing
        Else
            Return Format(CDec(value),"(###) ###-####") 
       End If
    End Function

     

    -- Robert

     

     

    Thursday, November 9, 2006 7:07 PM
    Moderator
  •  

    hi Robert

    I want to Sum on some Condition for Example

     if Employee Name="Mahesh" Then Sum the Values else do nothing. I am doing--

    =IIF(UCASE(Fields!EmpName.Value)= "MAHESH",SUM(CDEC(Fields!DecVal.Value)),"")

    but I am not getting desired output. Reply ASAP.

     

    Mahesh

     

     

    Wednesday, April 16, 2008 12:28 PM
  • >I want to Sum on some Condition for Example

    > if Employee Name="Mahesh" Then Sum the Values else do nothing. I am doing--

    >=IIF(UCASE(Fields!EmpName.Value)= "MAHESH",SUM(CDEC(Fields!DecVal.Value)),"")

    >but I am not getting desired output. Reply ASAP.


    Mahesh, Try out this. It should work.

    =SUM(IIF(UCASE(Fields!EmpName.Value)="MAHESH",CDEC(Fields!DecVal.Value),0))

    Thursday, March 26, 2009 11:57 AM
  • Robert,

    I realize this is an old post, but I am trying to do what you suggested with a date field where my Else Return is slightly different, and still I am getting the dreaded #Error in the fields that are Null.

    The exact return that I'm doing is Return CDate(value).ToShortDateString()

    Any help would be appreciated.

    Thanks
    Pat
    Tuesday, February 23, 2010 4:44 PM
  • Hello Pat,

    it seems you are using DateTime values in your case.  Did you verify that the values are really null when you get #Error?  What if you add another textbox (just for testing purposes) in your report with an RDL expression like this:
    =IsNothing(Fields!MyDateValue.Value)

    Does the textbox show "true" for those DateTime values where you get #Error returned from the custom code function?

    HTH,
    Robert


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, February 24, 2010 2:18 AM
    Moderator
  • HI Everyone, i happen to know the awnser to the #error problem, its not because its null, a null value displays as blank, you get #error when you try to format a null value, first check if its null and then format the value to whatever
    • Proposed as answer by StevenRay Friday, October 8, 2010 2:48 PM
    Friday, September 17, 2010 11:58 AM
  • This works For Me

    Set the PhoneNumber Value to something like this;

    =Code.ConvertValue(Fields!Phone.Value)

    Put this code in the Custom Code.

     

    Public Function ConvertValue(ByVal value As Object) As String
        If IsNothing(value) or Len(Trim(value))=0 Then
            Return Nothing
        Else
     Return Format(CDec(value),"(###) ###-####")
       End If
    End Function

    Friday, October 8, 2010 2:51 PM
  • Trim trims Null value to empty string and that does the magic:

    =IIf(Trim(Fields!PhCell.Value) = "", "NULL", "NOT NULL")

    Thursday, October 31, 2013 3:02 PM
  • I realise this is a couple of years later but Thanks!!
    Wednesday, February 4, 2015 2:06 PM