Formating a numeric string, that is sometimes text

The old string didn't move up when I replied to it, so copied the information
into this new question
----
OK -- this works to a point. I have real string data in my field that I want
to display, if the field is not "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!
> >>
> >>
> >>
>
>
>This will happen because of the nature of IIF. IIF calculates both the true
and false values before choosing which one to display. So it actually is
failing because in the background it trys to convert the string to a double
and return the value. Writing Custom Code to take advantage of the real IF
function is a way around this.
If you know VB its pretty simple.
report->report properties-> code tab->
Public Function checkstr(ByVal testval As String)
IF Char.IsNumber(testval, 0) THEN
return CDbl(testval)
ELSE
return "N/A"
END IF
End Function
Then apply the formatting in the textbox you want N/A Displayed in and have
your text box state the following
=Code.checkstr(Fields!Q4Amt.Value)
-Michael Abair
Programmer Analyst
Chicos FAS Inc.
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
> The old string didn't move up when I replied to it, so copied the
> information
> into this new question
> ----
> OK -- this works to a point. I have real string data in my field that I
> want
> to display, if the field is not "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 like your solution better than mine Michael... kudos! :)
=-Chris
"Michael Abair" <michael.abair@.chicos.com> wrote in message
news:OTxLAxH9GHA.4224@.TK2MSFTNGP02.phx.gbl...
> This will happen because of the nature of IIF. IIF calculates both the
> true and false values before choosing which one to display. So it
> actually is failing because in the background it trys to convert the
> string to a double and return the value. Writing Custom Code to take
> advantage of the real IF function is a way around this.
> If you know VB its pretty simple.
> report->report properties-> code tab->
>
> Public Function checkstr(ByVal testval As String)
> IF Char.IsNumber(testval, 0) THEN
> return CDbl(testval)
> ELSE
> return "N/A"
> END IF
> End Function
> Then apply the formatting in the textbox you want N/A Displayed in and
> have your text box state the following
> =Code.checkstr(Fields!Q4Amt.Value)
> -Michael Abair
> Programmer Analyst
> Chicos FAS Inc.
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
>> The old string didn't move up when I replied to it, so copied the
>> information
>> into this new question
>> ----
>> OK -- this works to a point. I have real string data in my field that I
>> want
>> to display, if the field is not "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!
>> >>
>> >>
>> >>
>>
>>
>|||Thank you Michael. Didn't quite do to trick, but put me in the right
direction.
This is the code I ended up with 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 I called it like you wrote in the field and Viola!!! It worked.
Thank you ever so much.
"Michael Abair" wrote:
> This will happen because of the nature of IIF. IIF calculates both the true
> and false values before choosing which one to display. So it actually is
> failing because in the background it trys to convert the string to a double
> and return the value. Writing Custom Code to take advantage of the real IF
> function is a way around this.
> If you know VB its pretty simple.
> report->report properties-> code tab->
>
> Public Function checkstr(ByVal testval As String)
> IF Char.IsNumber(testval, 0) THEN
> return CDbl(testval)
> ELSE
> return "N/A"
> END IF
> End Function
> Then apply the formatting in the textbox you want N/A Displayed in and have
> your text box state the following
> =Code.checkstr(Fields!Q4Amt.Value)
> -Michael Abair
> Programmer Analyst
> Chicos FAS Inc.
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
> > The old string didn't move up when I replied to it, so copied the
> > information
> > into this new question
> > ----
> >
> > OK -- this works to a point. I have real string data in my field that I
> > want
> > to display, if the field is not "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!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >
>
>|||Much Appreciated!
"Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
news:O0$$x0H9GHA.3264@.TK2MSFTNGP04.phx.gbl...
>I like your solution better than mine Michael... kudos! :)
> =-Chris
> "Michael Abair" <michael.abair@.chicos.com> wrote in message
> news:OTxLAxH9GHA.4224@.TK2MSFTNGP02.phx.gbl...
>> This will happen because of the nature of IIF. IIF calculates both the
>> true and false values before choosing which one to display. So it
>> actually is failing because in the background it trys to convert the
>> string to a double and return the value. Writing Custom Code to take
>> advantage of the real IF function is a way around this.
>> If you know VB its pretty simple.
>> report->report properties-> code tab->
>>
>> Public Function checkstr(ByVal testval As String)
>> IF Char.IsNumber(testval, 0) THEN
>> return CDbl(testval)
>> ELSE
>> return "N/A"
>> END IF
>> End Function
>> Then apply the formatting in the textbox you want N/A Displayed in and
>> have your text box state the following
>> =Code.checkstr(Fields!Q4Amt.Value)
>> -Michael Abair
>> Programmer Analyst
>> Chicos FAS Inc.
>>
>> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
>> news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
>> The old string didn't move up when I replied to it, so copied the
>> information
>> into this new question
>> ----
>> OK -- this works to a point. I have real string data in my field that I
>> want
>> to display, if the field is not "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 know, it never even occured to me that the logical IIF would do that...
that is evaluate the expression, and the false case...
i.e.
if a = 5 then is this fine...
=iif(IsNumeric(a), Format(Convert.ToDouble(a), "#"), "N/A")
if a = 'test' I would expect "N/A" to print out...
Of course I HAD to create a report to test it... and sure enough, it broke
the moment I put a text character in instead of a value.
Thanks for the great tip.
=-Chris
"Michael Abair" <michael.abair@.chicos.com> wrote in message
news:OTxLAxH9GHA.4224@.TK2MSFTNGP02.phx.gbl...
> This will happen because of the nature of IIF. IIF calculates both the
> true and false values before choosing which one to display. So it
> actually is failing because in the background it trys to convert the
> string to a double and return the value. Writing Custom Code to take
> advantage of the real IF function is a way around this.
> If you know VB its pretty simple.
> report->report properties-> code tab->
>
> Public Function checkstr(ByVal testval As String)
> IF Char.IsNumber(testval, 0) THEN
> return CDbl(testval)
> ELSE
> return "N/A"
> END IF
> End Function
> Then apply the formatting in the textbox you want N/A Displayed in and
> have your text box state the following
> =Code.checkstr(Fields!Q4Amt.Value)
> -Michael Abair
> Programmer Analyst
> Chicos FAS Inc.
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
>> The old string didn't move up when I replied to it, so copied the
>> information
>> into this new question
>> ----
>> OK -- this works to a point. I have real string data in my field that I
>> want
>> to display, if the field is not "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!
>> >>
>> >>
>> >>
>>
>>
>