Formatting number

I have a phone number as 0396526888. if I were to format this to say +61 3
9652 6888 how would I go about working on this.
Any help would be helpful. thanks heaps
Regards
RidhimaOn Sep 3, 12:26 am, Ridhima Sood
<RidhimaS...@.discussions.microsoft.com> wrote:
> I have a phone number as 0396526888. if I were to format this to say +61 3
> 9652 6888 how would I go about working on this.
> Any help would be helpful. thanks heaps
> Regards
> Ridhima
Assuming that there was a field called PhoneNumber set to 0396526888,
you would use an expression similar to the following:
= "+61 " + Left(Fields!PhoneNumber.Value, 2) + " " + Fields!
PhoneNumber.Value.Substring(3, 4) + " " + Right(Fields!
PhoneNumber.Value, 4)
Of course, you could use the 'Mid' function in place of the
'Substring' function. Also, you might want to use CStr(CInt(...))
around the 'Left' part of the expression to get rid of the zero.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You could also simply use the following format code:
+61 ## #### ####
Assuming you have a static length (10) and you will alway append +61.
Let me know if you have questions...
"EMartinez" wrote:
> On Sep 3, 12:26 am, Ridhima Sood
> <RidhimaS...@.discussions.microsoft.com> wrote:
> > I have a phone number as 0396526888. if I were to format this to say +61 3
> > 9652 6888 how would I go about working on this.
> >
> > Any help would be helpful. thanks heaps
> >
> > Regards
> > Ridhima
>
> Assuming that there was a field called PhoneNumber set to 0396526888,
> you would use an expression similar to the following:
> = "+61 " + Left(Fields!PhoneNumber.Value, 2) + " " + Fields!
> PhoneNumber.Value.Substring(3, 4) + " " + Right(Fields!
> PhoneNumber.Value, 4)
> Of course, you could use the 'Mid' function in place of the
> 'Substring' function. Also, you might want to use CStr(CInt(...))
> around the 'Left' part of the expression to get rid of the zero.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql

Formatting for Phone?

In my report I am combing 2 fields as

=Fields!PROPERTY.Value & " - " & Fields!PHONE.Value

this results in PropertyName - 5555551212

How would I add in formatting for phone so my resulting display is (555) 555-1212?

Jim Seidel wrote:

In my report I am combing 2 fields as

=Fields!PROPERTY.Value & " - " & Fields!PHONE.Value

this results in PropertyName - 5555551212

How would I add in formatting for phone so my resulting display is (555) 555-1212?

I'm doing this from memory, but something like this:

= "(" & Left(Fields!PHONE.Value,3) & ") " & Right(Left(Fields!PHONE.Value,6),3) & "-" & Right(Fields!PHONE.Value,4)

The full expression would be this:

= Fields!PROPERTY.Value & " - " & "(" & Left(Fields!PHONE.Value,3) & ") " & Right(Left(Fields!PHONE.Value,6),3) & "-" & Right(Fields!PHONE.Value,4)|||Awesome Memory, it worked perfectly !|||Great, glad I could help. You'll get used to it. Does it make sense?

Getting the middle characters is a little awkward. I can't remember whether there is a Mid function defined or not, so I just use that.|||Yes, like you say, it just comes with practice

Formatting a textbox

I need to format a phone number parameter the user enters. Currently
when I print the parameter entered it displays like this ##########. I
want to format it to look like ###-###-####. Does anyone have any
ideas for making it look like that?E,
RS has a Custom Formatting built in where you can type in ###-###-####
for the Format.
But for phone Numbers the DB probably returns it as a string. You must
first convert it to a Number... a LONG in this case.
So go to report Properties go to Custom Code and put this in there:
Public Function convertToIntegerNow(tempVar2 As String)
convertToIntegerNow= CLng(tempVar2)
End Function
in the cell type in
=Code.convertToIntegerNow(Fields!Phone_Field_name.Value)
Then right click on the cell go to properties, change the format of the
cell to ###-###-####
Hope this helps
regards,
Stas K.

Formatting a Text box

I need to format a phone number parameter the user enters. Currently
when I print the parameter it displays like this ##########.
I want to format it to look like ###-###-####.
It can't be done with SQL, it must be done in the VB of that textbox.
Does anyone have any ideas' It would save me from pulling my last
hair out!!Down and Dirty, but:
=Mid(Fields!homephone.Value,1,3) & "-" & Mid(Fields!homephone.Value,4,3) &
"-" & Mid(Fields!homephone.Value,7,4)
You can substitute the Parameter value as Parameters!PHNumber.Value in place
of Fields!homephone.Value if they are entering the phone number in the
parameter box.
Rodney Landrum
"E" <ericfreiman@.mtgsi.com> wrote in message
news:1144097404.116802.178020@.v46g2000cwv.googlegroups.com...
>I need to format a phone number parameter the user enters. Currently
> when I print the parameter it displays like this ##########.
> I want to format it to look like ###-###-####.
> It can't be done with SQL, it must be done in the VB of that textbox.
> Does anyone have any ideas' It would save me from pulling my last
> hair out!!
>|||Hi,
Just select the cell you want to display with format right click and click
properties and select Format tab on the format field select "..." and click
custom and type "###-####"
Amarnath
"E" wrote:
> I need to format a phone number parameter the user enters. Currently
> when I print the parameter it displays like this ##########.
> I want to format it to look like ###-###-####.
> It can't be done with SQL, it must be done in the VB of that textbox.
> Does anyone have any ideas' It would save me from pulling my last
> hair out!!
>

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

format phone numbers

how can i format a phone number to be displayed as follows in the report
(678) 444-4444
Thanks!"rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> how can i format a phone number to be displayed as follows in the report
> (678) 444-4444
> Thanks!
Assuming all records are complete with 10 digits.
Try this:
=Format(cdec(PhoneNumber.Value),"(###) ###-####")|||= Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.' ####")
or use a regular expression, sth like
=System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
"(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> how can i format a phone number to be displayed as follows in the report
> (678) 444-4444
> Thanks!|||okay great. I used the first example. Thanks!
Now i have discovered that there are some rows without phone numbers.
How do I do an IIF statement, that will return the formatted phone number
and not available when there is no phone number?
"Teo Lachev [MVP]" wrote:
> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.' ####")
> or use a regular expression, sth like
> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> > how can i format a phone number to be displayed as follows in the report
> > (678) 444-4444
> >
> > Thanks!
>
>|||=Iif (Fields!phone.Value Is Nothing, Nothing,
Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.' ####"))
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> okay great. I used the first example. Thanks!
> Now i have discovered that there are some rows without phone numbers.
> How do I do an IIF statement, that will return the formatted phone number
> and not available when there is no phone number?
> "Teo Lachev [MVP]" wrote:
>> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
>> ####")
>> or use a regular expression, sth like
>> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
>> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
>> > how can i format a phone number to be displayed as follows in the
>> > report
>> > (678) 444-4444
>> >
>> > Thanks!
>>|||THANKS!!!!!!!!!
"rybrown1818" wrote:
> how can i format a phone number to be displayed as follows in the report
> (678) 444-4444
> Thanks!|||OK -- this works to a point. I have real string data in my field that I want
to display if the field !isnumeric. So, here is the code I am using:
=iif(isnumeric(Fields!Q4Amt.Value),
Format(convert.ToDouble(Fields!Q4Amt.Value),
"$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
Every time the N/A is supposed to be displayed, I get an error message.
Thanks for any and all help
"Teo Lachev [MVP]" wrote:
> =Iif (Fields!phone.Value Is Nothing, Nothing,
> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.' ####"))
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> > okay great. I used the first example. Thanks!
> > Now i have discovered that there are some rows without phone numbers.
> > How do I do an IIF statement, that will return the formatted phone number
> > and not available when there is no phone number?
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
> >> ####")
> >>
> >> or use a regular expression, sth like
> >>
> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> >> > how can i format a phone number to be displayed as follows in the
> >> > report
> >> > (678) 444-4444
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>|||You need to change the format of the textbox to be an expression - if the
value is NOT numeric, then the format is BLANK. You cannot dispaly regular
text for objects that are, in this case, formatted for numeric display.
Hope this helps.
=-Chris
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> OK -- this works to a point. I have real string data in my field that I
> want
> to display if the field !isnumeric. So, here is the code I am using:
> =iif(isnumeric(Fields!Q4Amt.Value),
> Format(convert.ToDouble(Fields!Q4Amt.Value),
> "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
> Every time the N/A is supposed to be displayed, I get an error message.
> Thanks for any and all help
> "Teo Lachev [MVP]" wrote:
>> =Iif (Fields!phone.Value Is Nothing, Nothing,
>> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
>> ####"))
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
>> > okay great. I used the first example. Thanks!
>> > Now i have discovered that there are some rows without phone numbers.
>> > How do I do an IIF statement, that will return the formatted phone
>> > number
>> > and not available when there is no phone number?
>> >
>> > "Teo Lachev [MVP]" wrote:
>> >
>> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
>> >> ####")
>> >>
>> >> or use a regular expression, sth like
>> >>
>> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
>> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
>> >>
>> >> --
>> >> HTH,
>> >> ---
>> >> Teo Lachev, MVP, MCSD, MCT
>> >> "Microsoft Reporting Services in Action"
>> >> "Applied Microsoft Analysis Services 2005"
>> >> Home page and blog: http://www.prologika.com/
>> >> ---
>> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
>> >> > how can i format a phone number to be displayed as follows in the
>> >> > report
>> >> > (678) 444-4444
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>|||I'm not sure what I want right now. I want to be able to display the "N/A"
and $123,456.78 in the same textbox. So my options are:
1) If you cannot display a string and a number in the same textbox, can you
superimpose one textbox on top of another, with a switch as to the visible
textbox, based on the expression IsHidden =iif(isnumeric(Fields!Q4Amt.Values),True, False
2) Write a parsing function in the Report Parameters Code window and return
ONLY string values
3) Punt...
Any other ideas would be appreciated.
"Chris Conner" wrote:
> You need to change the format of the textbox to be an expression - if the
> value is NOT numeric, then the format is BLANK. You cannot dispaly regular
> text for objects that are, in this case, formatted for numeric display.
> Hope this helps.
> =-Chris
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> > OK -- this works to a point. I have real string data in my field that I
> > want
> > to display if the field !isnumeric. So, here is the code I am using:
> >
> > =iif(isnumeric(Fields!Q4Amt.Value),
> > Format(convert.ToDouble(Fields!Q4Amt.Value),
> > "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
> >
> > Every time the N/A is supposed to be displayed, I get an error message.
> >
> > Thanks for any and all help
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> =Iif (Fields!phone.Value Is Nothing, Nothing,
> >> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
> >> ####"))
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> >> > okay great. I used the first example. Thanks!
> >> > Now i have discovered that there are some rows without phone numbers.
> >> > How do I do an IIF statement, that will return the formatted phone
> >> > number
> >> > and not available when there is no phone number?
> >> >
> >> > "Teo Lachev [MVP]" wrote:
> >> >
> >> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
> >> >> ####")
> >> >>
> >> >> or use a regular expression, sth like
> >> >>
> >> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> >> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> >> >>
> >> >> --
> >> >> HTH,
> >> >> ---
> >> >> Teo Lachev, MVP, MCSD, MCT
> >> >> "Microsoft Reporting Services in Action"
> >> >> "Applied Microsoft Analysis Services 2005"
> >> >> Home page and blog: http://www.prologika.com/
> >> >> ---
> >> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> >> >> > how can i format a phone number to be displayed as follows in the
> >> >> > report
> >> >> > (678) 444-4444
> >> >> >
> >> >> > Thanks!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Thanks Chris. I posted this twice, as this thread did not move up the forum,
when I replied to it. I didn't think anyone was watching it.
The answer I received was option #2
This is the code I place in the Report Property's Code Tab:
Public Function NumericStringToCurrencyString(ByVal strPhrase as string) as
string
if isnumeric(strPhrase) then
NumericStringToCurrencyString =format(cdec(strPhrase),"$###,###,###.##;($###,###,###.##);$0.00")
else
NumericStringToCurrencyString = strPhrase
end if
end function
Then in the textbox Expression field, I entered:
=Code.NumericStringToCurrencyString( Fields!Q4Amt.Value)
This gave me the desired results.
Thank you for your assistance
Thank you ever so much.
"msflinx" wrote:
> I'm not sure what I want right now. I want to be able to display the "N/A"
> and $123,456.78 in the same textbox. So my options are:
> 1) If you cannot display a string and a number in the same textbox, can you
> superimpose one textbox on top of another, with a switch as to the visible
> textbox, based on the expression IsHidden => iif(isnumeric(Fields!Q4Amt.Values),True, False
> 2) Write a parsing function in the Report Parameters Code window and return
> ONLY string values
> 3) Punt...
> Any other ideas would be appreciated.
> "Chris Conner" wrote:
> > You need to change the format of the textbox to be an expression - if the
> > value is NOT numeric, then the format is BLANK. You cannot dispaly regular
> > text for objects that are, in this case, formatted for numeric display.
> >
> > Hope this helps.
> >
> > =-Chris
> >
> >
> >
> > "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> > news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> > > OK -- this works to a point. I have real string data in my field that I
> > > want
> > > to display if the field !isnumeric. So, here is the code I am using:
> > >
> > > =iif(isnumeric(Fields!Q4Amt.Value),
> > > Format(convert.ToDouble(Fields!Q4Amt.Value),
> > > "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
> > >
> > > Every time the N/A is supposed to be displayed, I get an error message.
> > >
> > > Thanks for any and all help
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > >> =Iif (Fields!phone.Value Is Nothing, Nothing,
> > >> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
> > >> ####"))
> > >>
> > >> --
> > >> HTH,
> > >> ---
> > >> Teo Lachev, MVP, MCSD, MCT
> > >> "Microsoft Reporting Services in Action"
> > >> "Applied Microsoft Analysis Services 2005"
> > >> Home page and blog: http://www.prologika.com/
> > >> ---
> > >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> > >> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> > >> > okay great. I used the first example. Thanks!
> > >> > Now i have discovered that there are some rows without phone numbers.
> > >> > How do I do an IIF statement, that will return the formatted phone
> > >> > number
> > >> > and not available when there is no phone number?
> > >> >
> > >> > "Teo Lachev [MVP]" wrote:
> > >> >
> > >> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
> > >> >> ####")
> > >> >>
> > >> >> or use a regular expression, sth like
> > >> >>
> > >> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> > >> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> > >> >>
> > >> >> --
> > >> >> HTH,
> > >> >> ---
> > >> >> Teo Lachev, MVP, MCSD, MCT
> > >> >> "Microsoft Reporting Services in Action"
> > >> >> "Applied Microsoft Analysis Services 2005"
> > >> >> Home page and blog: http://www.prologika.com/
> > >> >> ---
> > >> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> > >> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> > >> >> > how can i format a phone number to be displayed as follows in the
> > >> >> > report
> > >> >> > (678) 444-4444
> > >> >> >
> > >> >> > Thanks!
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

format phone number

Hi Everyone,

I have a phone number coming from the database coming in the format of 2132563111. How can I do this in sql query
213-563-3111

Please let me know if there is any function that does it.

Thanks.

Data formatting is better handled on that front end. That being said, you could format the number using something like this:

SELECTLEFT(phone,3) +'-' +SUBSTRING(phone,4,3) +'-' +SUBSTRING(phone,7,99)AS formattedPhonedFROM yourTable

format phone field

Hello,

I have a phone number field with the format (123)-456-7890 I need to convert this to 1234567890 formats while I am retrieving data from the table. How can I do this?

My first thought is to use SUBSTRING(...) in your SELECT query. For example...

SELECT (SUBSTRING(PHONE,2,3) + SUBSTRING(PHONE,7,3) + SUBSTRING(PHONE,11,4)) AS PHONE FROM YOURTABLE

This assumes that all your phone records are in the format you described.

|||

Thanks for the reply, will this fail if there is nothing in Phone, if yes, hat can I do to solev that problem?

|||

SELECT REPLACE(REPLACE(REPLACE(Phone,'(',''),')',''),'-','') AS Phone

Basically that just removes all ()- from the phone field before returning it to you.

|||Do whatmotleysuggested. I'm not sure, but youmayneed to use ISNULL(PHONE,'') in place of PHONE if your PHONE column is nullable.

SELECT REPLACE(REPLACE(REPLACE(ISNULL(Phone,''),'(',''),')',''),'-','') AS Phone