EdS45
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.
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.
ASKER
Thanks milindsm but....
here is the line of code:
<td width="3%" nowrap class="style4"><div align="right"><%=if(rsPL_V IE_FEB10.F ields.Item ("B2").Val ue)=0 then
response.write "_"
elseif (rsPL_VIE_FEB10.Fields.Ite m("B2").Va lue) >0 then
response.write ("<FONT color=red>")
response.write (rsPL_VIE_FEB10.Fields.Ite m("B2").Va lue)
response.write ("</FONT>")
else
response.write (rsPL_VIE_FEB10.Fields.Ite m("B2").Va lue)
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
here is the line of code:
<td width="3%" nowrap class="style4"><div align="right"><%=if(rsPL_V
response.write "_"
elseif (rsPL_VIE_FEB10.Fields.Ite
response.write ("<FONT color=red>")
response.write (rsPL_VIE_FEB10.Fields.Ite
response.write ("</FONT>")
else
response.write (rsPL_VIE_FEB10.Fields.Ite
end if%></div></td>
and here is the error message:
Expected statement
/Reporting/PL_VIE.asp, line 310
=if(rsPL_VIE_FEB10.Fields.
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").Val ue
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
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
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.
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...!!!
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.
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
while calling the function,
<TD>
<%
printVal(rsPL_VIE_FEB10("B 2"))
%>
</TD>
<TD>
<%
printVal(rsPL_VIE_FEB10("B
%>
</TD>
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
One little thing: the negative values are now displayed (-12345) and it should be without the "-" sign, eg
(12345).
Thanks again
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The fiirst option did the trick. I will now accept the solution.
Thanks again for all.
Regards
Ed
Thanks again for all.
Regards
Ed
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(valNu m, len(valNum)-1))
response.write(")</FONT>")
else
response.write(valNum)
end if
End Sub
%>
A typical line of code is :
<%PrintVal(NullToZero(Form atNumber(( rsPL_DATA. Fields.Ite m("A1").Va lue), -1, -2, -2, -2))) %>
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(valNu
response.write(")</FONT>")
else
response.write(valNum)
end if
End Sub
%>
A typical line of code is :
<%PrintVal(NullToZero(Form
Open in new window