Formating for a phone number

I have a report in which I'd like to display a phone number.
Currrently, in SQL Server, the phone numbers appear as a string a numbers eg
"4255551212"
I'd like to display them with area code, then number such as "(425) 555-1212"
I've tried playing aroud with formating options and I'm stumped...
Can someone lend a hand?Hi, ReportDude,
Try
=Format(CDbl("4255551212"), "(000) 000-0000")
HTH,
Andrei.
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
> I have a report in which I'd like to display a phone number.
> Currrently, in SQL Server, the phone numbers appear as a string a numbers
eg
> "4255551212"
> I'd like to display them with area code, then number such as "(425)
555-1212"
> I've tried playing aroud with formating options and I'm stumped...
> Can someone lend a hand?|||Create a function i.e. and put it in the code tab section under report
properties.
public function FormatNumber(StrPhone as String) as String
dim sRetString as String = ""
if IsNothing(StrPhone)
sRetString = ""
else
sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
end if
return sRetString
end function
Then in your grid, lets say... for the expression of the text control...
Code.FormatNumber(Fields!Phone.Value)
Now the code I just gave you might have bugs in it - as I am in a hurry :)
So test it and correct where needed - but you get the idea.
=-Chris
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
>I have a report in which I'd like to display a phone number.
> Currrently, in SQL Server, the phone numbers appear as a string a numbers
> eg
> "4255551212"
> I'd like to display them with area code, then number such as "(425)
> 555-1212"
> I've tried playing aroud with formating options and I'm stumped...
> Can someone lend a hand?|||I get an error that says "There is an error on line 5 of custom code:
Expression Expected"
I don't understand this - the code does have an expression...
any ideas?
"Christopher Conner" wrote:
> Create a function i.e. and put it in the code tab section under report
> properties.
> public function FormatNumber(StrPhone as String) as String
> dim sRetString as String = ""
> if IsNothing(StrPhone)
> sRetString = ""
> else
> sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
> StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
> end if
> return sRetString
> end function
> Then in your grid, lets say... for the expression of the text control...
> Code.FormatNumber(Fields!Phone.Value)
> Now the code I just gave you might have bugs in it - as I am in a hurry :)
> So test it and correct where needed - but you get the idea.
> =-Chris
> "ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
> news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
> >I have a report in which I'd like to display a phone number.
> >
> > Currrently, in SQL Server, the phone numbers appear as a string a numbers
> > eg
> > "4255551212"
> >
> > I'd like to display them with area code, then number such as "(425)
> > 555-1212"
> >
> > I've tried playing aroud with formating options and I'm stumped...
> > Can someone lend a hand?
>
>|||That is because of a line break in the code... I have recopied it from dev
studio, copy this and paste it into the code block between the function name
and end function...
Dim sRetString As String = ""
If IsNothing(StrPhone) Then
sRetString = ""
Else
sRetString = "(" & StrPhone.Substring(0, 2) & ") " & StrPhone.Substring(3,
3) & "-" & StrPhone.Substring(6, 4)
End If
Return sRetString
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:B66A1226-50F4-484C-B203-8D047E97F523@.microsoft.com...
>I get an error that says "There is an error on line 5 of custom code:
> Expression Expected"
> I don't understand this - the code does have an expression...
> any ideas?
> "Christopher Conner" wrote:
>> Create a function i.e. and put it in the code tab section under report
>> properties.
>> public function FormatNumber(StrPhone as String) as String
>> dim sRetString as String = ""
>> if IsNothing(StrPhone)
>> sRetString = ""
>> else
>> sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
>> StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
>> end if
>> return sRetString
>> end function
>> Then in your grid, lets say... for the expression of the text control...
>> Code.FormatNumber(Fields!Phone.Value)
>> Now the code I just gave you might have bugs in it - as I am in a hurry
>> :)
>> So test it and correct where needed - but you get the idea.
>> =-Chris
>> "ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
>> news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
>> >I have a report in which I'd like to display a phone number.
>> >
>> > Currrently, in SQL Server, the phone numbers appear as a string a
>> > numbers
>> > eg
>> > "4255551212"
>> >
>> > I'd like to display them with area code, then number such as "(425)
>> > 555-1212"
>> >
>> > I've tried playing aroud with formating options and I'm stumped...
>> > Can someone lend a hand?
>>