ASP format number and values

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

milindsmCommented:
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

0
EdS45Author 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



0
milindsmCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

EdS45Author 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
0
milindsmCommented:
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.
0
milindsmCommented:
hey... take out that "=" sign before "if" ... that might be causing the problem...!!!
0
milindsmCommented:
hey... I just confirmed at my end.... that '=' seems to be the culprit...!!!
0
EdS45Author 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.
0
milindsmCommented:
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

0
milindsmCommented:
while calling the function,

<TD>
<%
printVal(rsPL_VIE_FEB10("B2"))
%>
</TD>
0
EdS45Author 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
0
milindsmCommented:
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>")
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:
The fiirst option did the trick. I will now accept the solution.
Thanks again for all.

Regards
Ed
0
EdS45Author 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))) %>
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.