aliashippysmom
Programmer
Hi all:
My program reads a MS SQL server table and uses CFFILE to make it into an excel spreadsheet so users can download it. Works fine, except for one small problem. The users want dollar amounts with no decimal points for fields which are dollar fields. Again, no problem. But for fields which contain dollar amounts, if they are blank (NULL), they want an n/a on the spreadsheet.
The following code does NOT work.
What I get in the spreadsheet for the "field1" value is $0n/a for when there is a null in the field.
If I don't use CFFILE and just output to a TABLE, it works fine!
What am I missing here and I hope this makes sense.
Thank you in advance.
My program reads a MS SQL server table and uses CFFILE to make it into an excel spreadsheet so users can download it. Works fine, except for one small problem. The users want dollar amounts with no decimal points for fields which are dollar fields. Again, no problem. But for fields which contain dollar amounts, if they are blank (NULL), they want an n/a on the spreadsheet.
The following code does NOT work.
Code:
<cfquery name="getData" datasource="ddd" >
select * from myFile
</cfquery>
<!---Write out column headers:--->
<cffile action="write" file="C:\Websites\files\myfile2.xls" output= '<table border=1>
<tr><td valign="top">ID</td>
<td valign="top">field1</td>
<td valign="top">field2</td>
<td valign="top">field3</td>
</tr>'
<cfoutput query="getData">
<cffile action="append"
file="C:\Websites\files/myfile2.xls"
output='
<tr><td valign="top">#ID#</td>
<td valign="top"><cfif len(field1)>#NumberFormat(field1,"$9")#<cfelse>n/a</cfif></td>
<td valign="top">#field2#</td>
<td valign="top">#field3#</td>
</tr>'
If I don't use CFFILE and just output to a TABLE, it works fine!
What am I missing here and I hope this makes sense.
Thank you in advance.