13

I'm having an issue and everything i've tried doesn't work. I have a phone number datafield that returns numbers with no formatting '3055558798' but i want it to look like this '(305)555-8798'. I can get that done with this expression:

= Format(Convert.ToDouble(Fields!MyFieldName.Value), "(###)###-####")

The only issue is that when the return is null i get #ERROR in the space. I found an expression that got rid of the #ERROR but still no luck putting them both together. I would have to dig through my reports to find the expression but hopefully someone can help me. I've been doing reports for a couple of months but i'm still not very good with all the expressions that there are. I just need to format the phone number and if the return is null then not show anything. There's also this on the same site that i found the expression but it doesn't work so i dont know why the guy said it worked for him.

=Iif (Fields!MyFieldName.Value Is Nothing, Nothing, 
Format(Convert.ToDouble(Fields!MyFieldName.Value), "(###)###-####"))

That just doesn't work for me, I believe the syntax is wrong but i don't know what to change to fix it. Thanks.

Rodney Maspoch
  • 955
  • 3
  • 13
  • 19
  • I tried this on simple DataSet based on `select MyFieldName = '3055558798' union all select MyFieldName = null` and the second expression worked as required with no error; seems like there's nothing inherently wrong with the expression itself. – Ian Preston Jul 02 '13 at 21:35

1 Answers1

31

The error you have got is nothing to do with formatting, it is the conversion to Double that is failing. So your expression works perfectly as long as your field consists entirely of numeric characters. However, you have some data with non-numeric characters in it, causing the Convert.ToDouble() function to throw an error.

Unfortunately this can not be solved with an IIF expression because IIF is a function, not a language construct so both the true and false parameters get evaluated before being passed to the function regardless of the value of the boolean condition parameter. This means that:

=IIF(IsNumeric(Fields!Phone.Value), Format(Convert.ToDouble(Fields!Phone.Value), "(###)###-####"), Fields!Phone.Value)

will always attempt the conversion to double regardless of the result of the IsNumeric function. There are two ways to solve this:

Use Val instead of ToDouble

The problem with ToDouble is it errors when the string to be converted is an inappropriate form; Val doesn't have that problem - it simply grabs whatever numbers it can. So now we can use the expression:

=IIF(Fields!Phone.Value Is Nothing, 
  Nothing, 
  IIF(IsNumeric(Fields!Phone.Value), 
    Format(Val(Fields!Phone.Value), "(000)000-0000"), 
    Fields!Phone.Value)
)

(Use 0 rather than # so that leading zeroes aren't suppressed)

This expression returns Nothing if the field is Null, checks to see if it is numeric and if so converts it to a number and formats it, otherwise it just returns whatever is in the field.

Note that the Val function is still being run even when the field is not numeric but this expression succeeds because the Val function doesn't raise errors like ToDouble. We simply make the calculation and discard the result.

Custom code

On the Report menu, click Report Properties... and go to the Code tab. Insert the following code:

Function FormatPhone(Phone AS String) AS String
   IF (Phone Is Nothing) Then
    Return Nothing
  Else If (IsNumeric(Phone)) Then
    Return Format(Convert.ToDouble(Phone), "(000)000-0000")
  Else
    Return Phone
  End If
End Function

Use the following expression in your phone number cell:

=Code.FormatPhone(Fields!Phone.Value)
Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • Chris thank you for that very well worded and explained reply. Unfortunately we're avoiding putting code in the reports so i might not be able to do what you said although i can see how that would fix my issue. I have to take another look because i think i always return numeric values but they're not always 10 digits, the formatting expression takes care of it regardless but does throw the #ERROR for null so maybe the code for that null return is helping to cause the issue. I'll still try your idea just to familiarize myself better with this. Thank you very much. – Rodney Maspoch Jul 03 '13 at 13:22
  • The format won't throw an error when it is less than or more than 10 digits. Based on your pattern, `30555587` will give `(3)055-5587` and `305555879800` will give `(30555)587-9800` so it fills from the right and puts the lessor or greater digits in the left hand part of the format. It can only be the conversion to double that is erroring due to invalid characters. – Chris Latta Jul 04 '13 at 02:35
  • 1
    I'm not sure why you have a prejudice against VBA code in the custom code area as opposed to VBA code in the Value expression - VBA is VBA no matter where it is. However, I found the Val conversion function that we can use that won't throw an error so you can have an expression that works. I've added this to the answer. – Chris Latta Jul 04 '13 at 02:50
  • Hi I used this information to cater for my Australian phone numbers that start with zero, I found # suppressed the leading zero. – MikeAinOz Mar 31 '15 at 05:22
  • =Iif(Fields!Telephone.Value Is Nothing,Format(Val(Fields!MobilePhone.Value),"0000 000 000") ,Format(Val(Fields!Telephone.Value),"00 0000 0000")) – MikeAinOz Mar 31 '15 at 05:23
  • 1
    It's working for me. I don't know why this answer haven't been accepted – Antoine Pelletier Jun 15 '16 at 19:37