Format Numbers

I still battling with this page. I now need to format numbers as percentage and also replacing resukting value if=0 to "dash", settings negative values in red and in parenthesis.
So far - and with help of the experts - I have these two functions which work perfectly - except on this one column where I have values to be displaey as % "Percent", Any help is appreciated.

Here are the two funtions:
<%function NullToZero(pData)
if len(pData) = 0 then
NullToZero = cdbl(0)
else
on error resume next
pData = cdbl(pData)
if not IsNumeric(pData) then pData = 0
NullToZero = cdbl(pData)
end if
end function
%>
<%
Sub PrintVal(valNum)
if valNum = 0 then
response.write("_")
elseif valNum < 0 then
response.write("<FONT color=red>(")
response.write(Right(valNum, len(valNum)-1))
response.write(")</FONT>")
else
response.write(valNum)
end if
End Sub
%>

And here is a typical line of code (the ones working):

<%PrintVal(FormatNumber(NullToZero(rsData_Actual.Fields.Item("A1").Value), 0)) %>
EdS45Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HugoHiaslCommented:
If the values are still in percent you can use the code below. If you need to change 0.39 to 39% then you need to multiply valNum with 100.

Sub PrintVal(valNum)
if valNum = 0 then
response.write("_")
elseif valNum < 0 then
response.write("<FONT color=red>(")
response.write(Right(valNum, len(valNum)-1) & " %")
response.write(")</FONT>")
else
response.write(valNum & " %")
end if
End Sub
0
EdS45Author Commented:
It is supposed to work with just this one function call? Like:
<%PrintVal(rsBudget_PC.Fields.Item("DiffA1").Value)%>

I get this error:
Type mismatch: 'valNum'
/Reporting/PL_data_org.asp, line 389

0
thehagmanCommented:
I assume asp stumbles over the % character. Try as below.
Btw. as a matter of taste I'd go for
<span style="color:red">(number)</span>
instead of
<FONT color=red>(number)</FONT>
Sub PrintVal(valNum)
if valNum = 0 then
response.write("_")
elseif valNum < 0 then
response.write("<FONT color=red>(")
response.write(Right(valNum, len(valNum)-1) & " " & Chr(37))
response.write(")</FONT>")
else
response.write(valNum & " " & Chr(37))
end if
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

EdS45Author Commented:
I gte this one now:

Object required: '[string: "-0"]'
/Reporting/PL_data_org.asp, line 517
0
EdS45Author Commented:
Your suggestion to change the font color as <span style="color:red">(number)</span>
does not work for me here as all the data is dynamic. Thus, only change to red if conditions are met.
0
ThinkPaperIT ConsultantCommented:
can you post more of your code? what exactly is the code on line 517?
0
hieloCommented:
try:
Sub PrintVal(valNum)
	If InStr(1,valNum,"%") Then
		valNum=Replace(valNum,"%","")
     	if valNum = 0 then
     		response.write("_")
     	elseif valNum < 0 then
     		response.write("<FONT color=red>(")
     		response.write(Right(valNum, len(valNum)-1) & "%")
     		response.write(")</FONT>")
     	else
     		response.write(valNum & "%")
     	end if
	Else
     	if valNum = 0 then
     		response.write("_")
     	elseif valNum < 0 then
     		response.write("<FONT color=red>(")
     		response.write(Right(valNum, len(valNum)-1))
     		response.write(")</FONT>")
     	else
     		response.write(valNum)
     	end if
	End If
End Sub

..
<%PrintVal(FormatNumber(NullToZero(rsData_Actual.Fields.Item("A1").Value), 0)) %>

Open in new window

0
EdS45Author Commented:
Perhaps my first question was over the top. Among al the columns with dynamic data, I have some that display data as "percentage" i.e. "-14.76%". Using the buil-tin "FormatPercentage" function I managed to convert data from Access as Percentage. So far so good. What I would like to accomplish is: when the value displays a negative percentage (eg. -14.76%), that it would be displayed in red.
The functions I have included above (PrintVal, NullToZero), I use in other columns for the display of "ordinary numbers". That works well. I fear that by using the PrintVal function for the "negative-red display in percentage" , bytes itself with the remaining functions.

As for a dtypical line of code, like 517, here it is:

<td width="3%" nowrap="NOWRAP" class="style4"><div align="right"><%= FormatPercent((rsBudget_PC.Fields.Item("DiffA3").Value), -1, -2, -2, -2) %></div></td>
0
hieloCommented:
>>when the value displays a negative percentage (eg. -14.76%), that it would be displayed in red.
What I posted above does exactly that, but it removes the hyphen. If you want the hyphen, change:
response.write(Right(valNum, len(valNum)-1) & "%")

to:
response.write(valNum & "%")
0
EdS45Author Commented:
hielo,
here is what I get displaying the result without the function: -0.147386925175111
using the built-in format percentage I get: -14.73%
using your function I only get:  "-" a dash!
0
hieloCommented:
>>here is what I get displaying the result without the function: -0.147386925175111
So the values you passed to the function will ALWAYS be percentages? In other words, if you were to call it as:
PrintVal(-0.147386925175111)
You expect: -14.73%

PrintVal(12)
You expect: 1200.00%

Is this correct?
0
hieloCommented:

Sub PrintVal(valNum)
	If InStr(1,valNum,"%") Then
		valNum=Replace(valNum,"%","")/100
	End If
	If valNum=0 Then
		Response.Write "_"
	ElseIf valNum < 0 Then
		response.Write "<font color='red'>" & FormatPercent(valNum,2) & "</font>"
	Else
		response.Write FormatPercent(valNum,2)
	End If
End Sub
PrintVal("0")
PrintVal("12%")
PrintVal("-13%")
PrintVal(-0.147386925175111)
PrintVal(0.147386925175111)

Open in new window

0
EdS45Author Commented:
Your solution probably ok, however, all the values are dynamic, in other words its taken from an access query populated with data. One of the queries calculates the percentage which is then retrieved by the page. I don't know in advamnce what values there will be. Thanks for the effort.
0
hieloCommented:
>>One of the queries calculates the percentage which is then retrieved by the page
My last post takes that into consideration.
0
EdS45Author Commented:
Hielo

I gave it a try. Is the line od code still like:
<%PrintVal(FormatNumber(NullToZero(rsBudget_PC.Fields.Item("DiffA1"), 0)).Value)%></div></td> ?

Cause I get :
Wrong number of arguments or invalid property assignment: 'NullToZero'
/Reporting/PL_data_org.asp, line 498

0
hieloCommented:

<%PrintVal( NullToZero( rsBudget_PC.Fields.Item("DiffA1").Value ) )%></div></td> ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EdS45Author Commented:
Yesssss. That's it ! Thanks a lot for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.