Link to home
Start Free TrialLog in
Avatar of EdS45
EdS45Flag for Switzerland

asked on

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.
Avatar of milindsm
milindsm
Flag of India image

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

Avatar of EdS45

ASKER

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



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
Avatar of EdS45

ASKER

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

ASKER

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

while calling the function,

<TD>
<%
printVal(rsPL_VIE_FEB10("B2"))
%>
</TD>
Avatar of EdS45

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of milindsm
milindsm
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EdS45

ASKER

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

Regards
Ed
Avatar of EdS45

ASKER

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