Excel Report stops converting currency

A

alien2_51

At some point after running this report the "Wholesale Price" column data
type in the excel spread sheet changes from Currency to General... It seems
to ba around the 300th or so row but varies... This problem only occurs if
the client is running Office XP.. And only in our production environment,
not test or dev they are supposed to identical to prod but I doubt that for
obvious reasons...

Can someone tell me why, or give some suggestions as to things to look
at.....???

ASP code;
<%@ LANGUAGE="VBSCRIPT" %>
<% Response.ContentType = "application/vnd.ms-excel"
%>

<!--#include virtual="/Include/odbvbs.asp"-->
<!--#INCLUDE VIRTUAL="/Include/wordcap.asp"-->

<%
dim sSql, sloctn, sbrand, sfield, sdate, rs

call ConnInit(cMOTOR)

sloctn = "" & request("txtloc")
sfield = "" & request("txtfield")
sdate = "" & request("txtdate")
sbrand = "" & request("txtbrand")

if sfield = "" then sfield = "E"

sSql = "dbo.msp_web_floored_units"

if sloctn = "" then
sSql = sSql & " null,"
else
sSql = sSql & " '" & sloctn & "',"
end if
if sdate = "" then
sSql = sSql & " null, '" & sfield & "'"
else
sSql = sSql & " '" & sdate & "', '" & sfield & "'"
end if
if sbrand = "" then
sSql = sSql & ", null"
else
sSql = sSql & ", '" & sbrand & "'"
end if

set rs = obMOTOR.Execute(sSql)
call QueueRS(rs)
%>

<HTML>
<HEAD>
<title>Floor Started Units Not Invoiced</title>
</HEAD>
<!--#INCLUDE VIRTUAL="/Styles/mncweb.css"-->

<body>
<%
if not rs.EOF then
%>
<table border=yes cellspacing=1 cellpadding=1
style="position:absolute;left:0;top:0;">
<caption>
<font size=3><b>Monaco Coach Corporation<br>
<font size=2>Floor Started Units Not Invoiced</font></b></font>
</caption>
<tr class=bold>
<td align=center valign=bottom style="font-size:8pt">Order No</td>
<td align=center valign=bottom style="font-size:8pt">Unit No</td>
<td align=center valign=bottom style="font-size:8pt">Prod No</td>
<td align=center valign=bottom style="font-size:8pt">Model Type</td>
<td align=center valign=bottom style="font-size:8pt">Dealer</td>
<td align=center valign=bottom style="font-size:8pt">Floor<br>Start</td>
<td align=center valign=bottom
style="font-size:8pt">Estimated<br>Off-Line</td>
<td align=center valign=bottom style="font-size:8pt">SickBay</td>
<td align=center valign=bottom style="font-size:8pt">Dispatch</td>
<td align=center valign=bottom style="font-size:8pt">Floored</td>
<td align=center valign=bottom style="font-size:8pt">Floorplan
Comments</td>
<td align=center valign=bottom style="font-size:8pt">Sales Comments</td>
<td align=center valign=bottom
style="font-size:8pt">Wholesale<br>Price</td>

</tr>
<%
dim ford, funit, fprod, ffloor, fsick, fdisp, fflrd, fcmnt, floc
dim fbase, fchas, fmdl, fopt, fdlr, fest, ftotsum, scmnt
dim counter
counter = 0
ftotsum = 0
ftot = 0
do while not rs.EOF
ford = "" & rs("ordno")
funit = rs("unitno")
fprod = "" & rs("prodno")
fmdl = "" & rs("model")
ffloor = "" & rs("floorst")
fest = "" & rs("eoffline")
fsick = "" & rs("sickbay")
fdisp = "" & rs("dispatch")
fflrd = "" & rs("floored")
fcmnt = "" & rs("fcomment")
floc = "" & rs("location")
fbase = rs("base")
fchas = rs("chassis")
fopt = rs("options")
fdlr = "" & rs("cusname")
scmnt = "" & rs("salescomment")

ftot = fbase + fchas + fopt

ftotsum = ftotsum + ftot

if unitno <> "" then
unitno = CStr(unitno)
'unitno = "'" & unitno
end if
if unitno = "" then unitno = "&nbsp;"
if fsick = "" then fsick = "&nbsp;"
if fdisp = "" then fdisp = "&nbsp;"
if fflrd = "" then fflrd = "&nbsp;"
if fest = "" then fest = "&nbsp;"
if fcmnt = "" then fcmnt = "&nbsp;"
if fmdl = "" then fmdl = "&nbsp;"
if scmnt = "" then scmnt = "&nbsp;"

%>
<tr><td align=left nowrap style="font-size:8pt"><%=ford%></td>
<td align=left nowrap style="font-size:8pt">'<%=funit%></td>
<td align=left nowrap style="font-size:8pt"><%=fprod%></td>
<td align=left nowrap style="font-size:8pt"><%=WordCap(trim(fmdl))%></td>
<td align=left nowrap style="font-size:8pt"><%=fdlr%></td>
<td align=left nowrap style="font-size:8pt"><%=ffloor%></td>
<td align=left nowrap style="font-size:8pt"><%=fest%></td>
<td align=left nowrap style="font-size:8pt"><%=fsick%></td>
<td align=left nowrap style="font-size:8pt"><%=fdisp%></td>
<td align=left nowrap style="font-size:8pt"><%=fflrd%></td>
<td align=left nowrap style="font-size:8pt"><%=fcmnt%></td>
<td align=left nowrap style="font-size:8pt"><%=scmnt%></td>
<td align=left nowrap style="font-size:8pt"><%=formatcurrency(ftot)%></td>

</tr>
<%
counter = counter + 1
rs.MoveNext
loop
%>
<tr><td align=left colspan=13 style="font-size:8pt">&nbsp;</td>
</tr>
<tr><td align=left colspan=11 style="font-size:8pt"><b>Number of
Units:</b>&nbsp;<b>&nbsp;<%= counter%></b></td>
<td align=left style="font-size:8pt"><b>Total:</b></td>
<td align=left nowrap
style="font-size:8pt"><b><%=formatcurrency(ftotsum)%></b></td>
</tr>
</table>
<%
else
%>
<table cellspacing=1 cellpadding=1>
<tr><td>No Units Found
</td>
</tr></table>
<%
end if
rs.close
obCC%>

</BODY>
</HTML>

sp;




CREATE procedure dbo.msp_web_floored_units (@loc char(06) = null,
@fdate datetime = null,
@dtype char(01),
@brand char(02) = null)
as


create table #floor
(ordno int,
unitno char(20),
prodno int,
cusgrp varchar(06),
cuscd varchar(10),
floorst datetime,
sickbay datetime,
dispatch datetime,
floored datetime,
fcomment char(25),
location char(06),
eoffline datetime,
base money,
chassis money,
options money,
cusname varchar(30),
model varchar(40),
brand char(02),
salescomment varchar(20))

if @loc is not null
begin
insert #floor
SELECT dbo.HR_ORDDET.ORDD_ORD_NO,
dbo.HR_ORDDET.ORDD_UNIT_NO,
dbo.HR_ORDDET.PROD_NO,
dbo.ord.ord_cus_grp,
dbo.ord.ord_cus_cd,
dbo.HR_ORDDET.ORDD_FLOOR_START_DT,
dbo.HR_ORDDET.ORDD_SICKBAY_DT,
dbo.HR_ORDDET.ORDD_DISPATCH_DT,
dbo.HR_ORDDET.ORDD_FLOORED_DT ,
dbo.HR_ORDDET.ORDD_FLOORED_CMNT,
dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION,
dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT,
IsNull(dbo.HR_ORDDET.ORDD_BASE_PRICE, 0.00) AS ORDD_BASE_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_CHASSIS_PRICE, 0.00) AS
ORDD_CHASSIS_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_OPTION_PRICE, 0.00) AS
ORDD_OPTION_PRICE,
null,
null,
dbo.model_master.mm_corp_cd,
dbo.ord.ord_cus_ord
FROM dbo.HR_ORDDET (nolock)
INNER JOIN dbo.ord (nolock) on dbo.HR_ORDDET.ORDD_ORD_CO =
dbo.ord.ord_company and dbo.HR_ORDDET.ORDD_ORD_NO = dbo.ord.ord_no
INNER JOIN dbo.model_master (nolock) on dbo.HR_ORDDET.ORDD_UNIT_NO =
dbo.model_master.sku_cd
WHERE ( (@dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not
null) or
(@dtype = 'F' and dbo.HR_ORDDET.ORDD_FLOOR_START_DT is not null))
and dbo.HR_ORDDET.ORDD_INVOICED_DT is null
and dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION = @loc

end
else
begin
insert #floor
SELECT dbo.HR_ORDDET.ORDD_ORD_NO,
dbo.HR_ORDDET.ORDD_UNIT_NO,
dbo.HR_ORDDET.PROD_NO,
dbo.ord.ord_cus_grp,
dbo.ord.ord_cus_cd,
dbo.HR_ORDDET.ORDD_FLOOR_START_DT,
dbo.HR_ORDDET.ORDD_SICKBAY_DT,
dbo.HR_ORDDET.ORDD_DISPATCH_DT,
dbo.HR_ORDDET.ORDD_FLOORED_DT ,
dbo.HR_ORDDET.ORDD_FLOORED_CMNT,
dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION,
dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT,
IsNull(dbo.HR_ORDDET.ORDD_BASE_PRICE, 0.00) AS ORDD_BASE_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_CHASSIS_PRICE, 0.00) AS
ORDD_CHASSIS_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_OPTION_PRICE, 0.00) AS
ORDD_OPTION_PRICE,
null,
null,
dbo.model_master.mm_corp_cd,
dbo.ord.ord_cus_ord
FROM dbo.HR_ORDDET (nolock)
INNER JOIN dbo.ord (nolock) ON dbo.HR_ORDDET.ORDD_ORD_CO =
dbo.ord.ord_company and dbo.HR_ORDDET.ORDD_ORD_NO = dbo.ord.ord_no
INNER JOIN dbo.model_master (nolock) ON dbo.HR_ORDDET.ORDD_UNIT_NO =
dbo.model_master.sku_cd
WHERE ( (@dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not
null) or
(@dtype = 'F' and dbo.HR_ORDDET.ORDD_FLOOR_START_DT is not null) )
and dbo.HR_ORDDET.ORDD_INVOICED_DT is null

end

if @fdate is not null
begin
if @dtype = 'E'
begin
delete #floor
where eoffline >= @fdate
end
else if @dtype = 'F'
begin
delete #floor
where floorst >= @fdate
end
end

update #floor
set cusname = b.cus_name
from #floor a (nolock)
inner join customer b (nolock)
on a.cuscd = b.cus_cd
and a.cusgrp = b.cus_grp_cd

update #floor
set model = b.model_desc

from #floor a (nolock)
inner join model b (nolock)
on substring(a.unitno,5,4) = b.model_code

if @brand is null
begin
select *
from #floor
order by location, cusname, model
end
else if @brand in ('HR','BC','SC')
begin
select *
from #floor
where brand = @brand
order by location, cusname, model
end
else if @brand = 'MC'
begin
select *
from #floor
where brand = 'MC'
and substring(unitno,5,4) > '1500'
order by location, cusname, model
end
else if @brand = 'MK'
begin
select *
from #floor
where brand = 'MC'
and substring(unitno,5,4) < '1500'
order by location, cusname, model
end






GO
 
A

alien2_51

I figured out what's happening... The data in an unrelated column contains a
"-" sign when Excel encounters and I assume tries to determine its datatype
it can't continue and fails to determine the correct data type for the
remaining columns and rows...

This seems to be a Microsoft bug in Office XP... Does anyone know of this...
Where can I submit...?

alien2_51 said:
At some point after running this report the "Wholesale Price" column data
type in the excel spread sheet changes from Currency to General... It seems
to ba around the 300th or so row but varies... This problem only occurs if
the client is running Office XP.. And only in our production environment,
not test or dev they are supposed to identical to prod but I doubt that for
obvious reasons...

Can someone tell me why, or give some suggestions as to things to look
at.....???

ASP code;
<%@ LANGUAGE="VBSCRIPT" %>
<% Response.ContentType = "application/vnd.ms-excel"
%>

<!--#include virtual="/Include/odbvbs.asp"-->
<!--#INCLUDE VIRTUAL="/Include/wordcap.asp"-->

<%
dim sSql, sloctn, sbrand, sfield, sdate, rs

call ConnInit(cMOTOR)

sloctn = "" & request("txtloc")
sfield = "" & request("txtfield")
sdate = "" & request("txtdate")
sbrand = "" & request("txtbrand")

if sfield = "" then sfield = "E"

sSql = "dbo.msp_web_floored_units"

if sloctn = "" then
sSql = sSql & " null,"
else
sSql = sSql & " '" & sloctn & "',"
end if
if sdate = "" then
sSql = sSql & " null, '" & sfield & "'"
else
sSql = sSql & " '" & sdate & "', '" & sfield & "'"
end if
if sbrand = "" then
sSql = sSql & ", null"
else
sSql = sSql & ", '" & sbrand & "'"
end if

set rs = obMOTOR.Execute(sSql)
call QueueRS(rs)
%>

<HTML>
<HEAD>
<title>Floor Started Units Not Invoiced</title>
</HEAD>
<!--#INCLUDE VIRTUAL="/Styles/mncweb.css"-->

<body>
<%
if not rs.EOF then
%>
<table border=yes cellspacing=1 cellpadding=1
style="position:absolute;left:0;top:0;">
<caption>
<font size=3><b>Monaco Coach Corporation<br>
<font size=2>Floor Started Units Not
Click to expand...
Invoiced said:
</caption>
<tr class=bold>
<td align=center valign=bottom style="font-size:8pt">Order No</td>
<td align=center valign=bottom style="font-size:8pt">Unit No</td>
<td align=center valign=bottom style="font-size:8pt">Prod No</td>
<td align=center valign=bottom style="font-size:8pt">Model Type</td>
<td align=center valign=bottom style="font-size:8pt">Dealer</td>
<td align=center valign=bottom style="font-size:8pt">Floor<br>Start</td>
<td align=center valign=bottom
style="font-size:8pt">Estimated<br>Off-Line</td>
<td align=center valign=bottom style="font-size:8pt">SickBay</td>
<td align=center valign=bottom style="font-size:8pt">Dispatch</td>
<td align=center valign=bottom style="font-size:8pt">Floored</td>
<td align=center valign=bottom style="font-size:8pt">Floorplan
Comments</td>
<td align=center valign=bottom style="font-size:8pt">Sales Comments</td>
<td align=center valign=bottom
style="font-size:8pt">Wholesale<br>Price</td>

</tr>
<%
dim ford, funit, fprod, ffloor, fsick, fdisp, fflrd, fcmnt, floc
dim fbase, fchas, fmdl, fopt, fdlr, fest, ftotsum, scmnt
dim counter
counter = 0
ftotsum = 0
ftot = 0
do while not rs.EOF
ford = "" & rs("ordno")
funit = rs("unitno")
fprod = "" & rs("prodno")
fmdl = "" & rs("model")
ffloor = "" & rs("floorst")
fest = "" & rs("eoffline")
fsick = "" & rs("sickbay")
fdisp = "" & rs("dispatch")
fflrd = "" & rs("floored")
fcmnt = "" & rs("fcomment")
floc = "" & rs("location")
fbase = rs("base")
fchas = rs("chassis")
fopt = rs("options")
fdlr = "" & rs("cusname")
scmnt = "" & rs("salescomment")

ftot = fbase + fchas + fopt

ftotsum = ftotsum + ftot

if unitno <> "" then
unitno = CStr(unitno)
'unitno = "'" & unitno
end if
if unitno = "" then unitno = "&nbsp;"
if fsick = "" then fsick = "&nbsp;"
if fdisp = "" then fdisp = "&nbsp;"
if fflrd = "" then fflrd = "&nbsp;"
if fest = "" then fest = "&nbsp;"
if fcmnt = "" then fcmnt = "&nbsp;"
if fmdl = "" then fmdl = "&nbsp;"
if scmnt = "" then scmnt = "&nbsp;"

%>
<tr><td align=left nowrap style="font-size:8pt"><%=ford%></td>
<td align=left nowrap style="font-size:8pt">'<%=funit%></td>
<td align=left nowrap style="font-size:8pt"><%=fprod%></td>
<td align=left nowrap
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can anyone please help? HTML - two tables applying different styles 4
Can someone tell me if this a real tracker? Or is it one designed to show you a different message at certain times, ie. acting like one? 0
Image shifts to the right when export the page to pdf 4
SendGrid email issue in responsive Gmail 1
How to have two html audio players on one page? 0
Help with Visual Lightbox: Scripts 2
Help with my responsive home page 2
I need help fixing my website 2

Members online

Total: 36 (members: 2, guests: 34)
Robots: 131

Forum statistics

Threads
473,772
Messages
2,569,593
Members
45,113
Latest member
Vinay KumarNevatia

Latest Threads

Top