ASP format number and values

EdS45
EdS45 used Ask the Experts™
on
I have an ASP page retrieving data from Access (in total 153 rows and 3columns) and need a function to format the resulting values as follows: for "0" display "dash" and for negative values change font to red.
I found somewhere else in here a solution for one of the items:
<table>
  <tr>
    <td><% if rs("value") = 0 then
        response.write "-"
    else
        response.write rs("value")
    end if%></td>
  </tr>
</table>

however it is not practical to assign that code to every cell. Appreciate any help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Can you try this,
<table>
  <tr>
    <td><% if rs("value") = 0 then
        response.write "-"
    elseif rs("value") < 0 then
        response.write("<FONT color=red>")
        response.write rs("value")
        response.write("</FONT>")
    else
        response.write rs("value")
    end if%></td>
  </tr>
</table>

Open in new window

Author

Commented:
Thanks milindsm but....

here is the line of code:

<td width="3%" nowrap class="style4"><div align="right"><%=if(rsPL_VIE_FEB10.Fields.Item("B2").Value)=0 then
response.write "_"
elseif (rsPL_VIE_FEB10.Fields.Item("B2").Value) >0 then
response.write ("<FONT color=red>")
response.write (rsPL_VIE_FEB10.Fields.Item("B2").Value)
response.write ("</FONT>")
else
response.write (rsPL_VIE_FEB10.Fields.Item("B2").Value)
end if%></div></td>

and here is the error message:

Expected statement
/Reporting/PL_VIE.asp, line 310
=if(rsPL_VIE_FEB10.Fields.Item("B2").Value)=0 then



Commented:
I would suggest go step-by-step....
Initially add only first "if" and check... btw, what is rsPL_VIE_FEB10 ???

Can you try using just

rsPL_VIE_FEB10("B2")

rather than

rsPL_VIE_FEB10.Fields.Item("B2").Value
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

Commented:
Sorry, still no-go

Here is the line now:

<td width="3%" ><div align="right"><%=if rsPL_VIE_FEB10("B2")=0 then
response.write "_"
elseif rsPL_VIE_FEB10("B2") <0 then
response.write ("<FONT color=red>")
response.write rsPL_VIE_FEB10("B2")
response.write ("</FONT>")
else
response.write rsPL_VIE_FEB10("B2")
end if%></div></td>

and here the error:

Expected statement
/Reporting/PL_VIE.asp, line 310
=if rsPL_VIE_FEB10("B2")=0 then

Commented:
Did you try step-by-step??? I hope there is really a field in your database table with the name "B2" and your recordset rsPL_VIE_FEB10 is populated.
You can try one more thing, instead of using field name (B2), try using an index value for that field.

Commented:
hey... take out that "=" sign before "if" ... that might be causing the problem...!!!

Commented:
hey... I just confirmed at my end.... that '=' seems to be the culprit...!!!

Author

Commented:
Yes, that was the problem.
Is it possible to inegrate the code into a function? I have hundreds of cells and it will be quite tedious to write the code in each and every one of them.
Another question: in case of negative numbers I also need to format the values to display in () parenthesis. can that be integrated?

Thanks o much for your quick and constructive help.

Commented:
Yes, you can do it, add this function at top within <% %>


Sub PrintVal(valNum)
if valNum = 0 then
response.write("-")
elseif valNum < 0 then
response.write("<FONT color=red>(")
response.write(valNum)
response.write(")</FONT>")
else
response.write(valNum)
end if
End Sub

Open in new window

Commented:
while calling the function,

<TD>
<%
printVal(rsPL_VIE_FEB10("B2"))
%>
</TD>

Author

Commented:
Works like a charm!.

One little thing: the negative values are now displayed (-12345) and it should be without the "-" sign, eg
(12345).

Thanks again
Commented:
response.write("<FONT color=red>(")
response.write(Right(valNum, len(valNum)-1))
response.write(")</FONT>")

OR

 one more silly method would be :)

response.write("<FONT color=red>(")
response.write(valNum*-1)
response.write(")</FONT>")

Author

Commented:
The fiirst option did the trick. I will now accept the solution.
Thanks again for all.

Regards
Ed

Author

Commented:
milindsm

I'm not really sure if we can carry on this thread or do I need to start a fresh one.
I'm asorry to bother again. I now realize that I need a more complex formatting. Though your solution did the trick with some of the issues, I still have some.
Here it is:
I have in the Access DB, fields with NULL. These I must replace on my page with "-" (dash).  We solved that problem for when I have a "0" which is then replaced by "-" dash. Remains the problem with the "Null" situation.
I also need to format all values as #,##0 in other words : no decimals.
Right now I have a combination of three fuinctions qhich I will display below - however they seem to "bite" each other. Appreciate all you can do.

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

A typical line of code is :
<%PrintVal(NullToZero(FormatNumber((rsPL_DATA.Fields.Item("A1").Value), -1, -2, -2, -2))) %>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial