Formatting numeric values in MagicCell

[Frank]

A question about how to format numeric values like the Excel cells such that when positive, it is black and when negative, it is red and in (..). All numbers are formatted as XXX.0. Change one of your NWIND Orders table - Freight record to negative and try the following code -

<%
Set X = Server.CreateObject("ASP.db")
X.dbUnit = 2
X.dbDSN = "NWIND"
X.dbMode = "grid"
'... no color X.dbMagicCell = "(~?[]!|~/+{})Freight??!Freight::#,##0;(###.#)!"
X.dbMagicCell = "(~?[]!|%/+{})Freight??(!freight!|LTN|0|align=right|<font color=red>!Freight::#,###.0;(#,###.0)!</font>%!Freight!|GEN|0|align=right|!Freight::#,###. 0!)"
X.dbSql = "select orderid, freight from orders"
X.ASPdb
%>

The commented line is one that has no color when negative. The actual line has -32.1 -> (32.1) red color. Note that the VB formatting function has a bug and produce -(32.1) instead of (32.1) when used w/o the miltiple +/- formatting. So use the +/- format to make it work. Of course, now you have total control of the color when +ve and -ve. The example is in classic ASP-db.

[Frank]

See the following code (included in the on-line manual magiccell page) ->

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim x As New Tornado.z
x.dbQP = "u=1| D=NWIND| Q=Select orderid, freight from orders"
'...x.dbMagicCell = "(~?[]!|~/+{})fi=freight| tag=align=right class=red"
'...x.dbMagicCell = "(~?[]!|~/+{})fi=1| tag=align=right| mac=!1:#,###.0;(#,###.0)!"
x.dbMagicCell = "(~?[]!|~/+{})fi=1| mac=(!1!|LTN|0|align=right|<font color=red>!1:#,###.0;(#,###.0)!</font>~|EL||align=right)"
x.ASPdbNET()
End Sub
</script>

Note that the Tornado MC parser is broken by the special case (-ve number) and is fixed in the July 2007 DLL. Classic ASP-db has no such problem.