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 = " "
if fsick = "" then fsick = " "
if fdisp = "" then fdisp = " "
if fflrd = "" then fflrd = " "
if fest = "" then fest = " "
if fcmnt = "" then fcmnt = " "
if fmdl = "" then fmdl = " "
if scmnt = "" then scmnt = " "
%>
<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"> </td>
</tr>
<tr><td align=left colspan=11 style="font-size:8pt"><b>Number of
Units:</b> <b> <%= 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 1 1909
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" <da********@n.o.s.p.a.m.monacocoach.com> wrote in message
news:##**************@TK2MSFTNGP10.phx.gbl... 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 = " " if fsick = "" then fsick = " " if fdisp = "" then fdisp = " " if fflrd = "" then fflrd = " " if fest = "" then fest = " " if fcmnt = "" then fcmnt = " " if fmdl = "" then fmdl = " " if scmnt = "" then scmnt = " "
%> <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"> </td> </tr> <tr><td align=left colspan=11 style="font-size:8pt"><b>Number of Units:</b> <b> <%= 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
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
5 posts
views
Thread by middletree |
last post: by
|
2 posts
views
Thread by mr.kuhl |
last post: by
|
9 posts
views
Thread by Edward S |
last post: by
| | |
15 posts
views
Thread by =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= |
last post: by
|
reply
views
Thread by amiga500 |
last post: by
|
15 posts
views
Thread by patf |
last post: by
| | | | | | | | | | | |