How to reformat a date sent via an Access Report To Excel ? 2

[GarHeard]

I execute the following stored procedure to create 3 tables. I then created an Access Report and read the last table created named tblRange as the Report: Record Source.
In the Header section, I have 2 text boxes. 1 text box has a control source of DFrC which is a field in tblRange. The other text box has a control source of DToC which is yet another field in tblRange. I notice that in the properties of tblRange that DFrC and DToC each have a data type of char(10).

When I execute the stored procedure and the Report is generated via the following command to send the reports to Excel:
DoCmd.OutputTo acOutputReport, "RptSummaryReport", acFormatXLS, ExportedFile

these 2 text boxes are displayed as numbers in Excel as follows:
DFrC displays as 38292 even though in the tblRange it displays as 2004-11-01.
DToC displays as 38291 even though in the tblRange it displays as 2005-04-30.

Do you know how I can fix these dates ?


CREATE PROCEDURE procDateRngRpt
AS

If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblCust' AND TYPE = 'U')
DROP TABLE tblCust

SELECT tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
Sum(isnull(MarketValue,0)+isnull(CashBalance,0)) AS AcctValue,
CONVERT(char(10), GETDATE() - Day(GETDATE()), 121) AS DFrom,
CONVERT(char(10), DATEADD(Month, -[MthTo], GETDATE() - Day(GETDATE()) + 1), 121) AS DateFromC,
tblMthRanges.MthTo,
CONVERT(char(10),DATEADD(DAY, -1, DATEADD(MONTH, -MthFrom, CONVERT(varchar(8), GETDATE(), 102)+ '01')), 121) AS DateToC
INTO tblCust
FROM tblMthRanges, tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber=tblProducts.CustomerNumber
WHERE (((DATEDIFF(M, [DateLost], DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) >= [MthFrom])) AND
((DateDiff(M,[DateLost],DATEADD(DD, (DATEPART(DD, GETDATE()) -1) * -1, GETDATE())) < [MthTo])))
GROUP BY tblMthRanges.MthFrom, tblMthRanges.MthTitle, tblCustomers.CustomerNumber,
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())), tblMthRanges.MthTo,
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()))

If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblSumm' AND TYPE = 'U')
DROP TABLE tblSumm

SELECT tblCust.MthFrom, tblCust.MthTitle, tblDollarRanges.DollarTitle, Count(CustomerNumber) AS CountOfCustomerNumber, Sum(AcctValue) AS SumOfAcctValue,
tblCust.DFrom, tblCust.DateFromC, tblCust.DateToC, tblDollarRanges.DollarFrom
INTO tblSumm
FROM tblCust, tblDollarRanges
WHERE (((.tblCust.AcctValue)>=[DollarFrom] And (tblCust.AcctValue)<[DollarTo]))
GROUP BY tblCust.MthFrom, tblCust.MthTitle, tblDollarRanges.DollarTitle, tblCust.DFrom, tblCust.DateFromC, tblCust.DateToC, tblDollarRanges.DollarFrom

If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblRange' AND TYPE = 'U')
DROP TABLE tblRange

SELECT Q.MthFrom As MthF, Q.MthTitle As MthTitl, DR.DollarTitle as DollTitl,
COUNT(Q.CustomerNumber) As CountOfCustomerNumber,
SUM(Q.AcctValue) AS SumOfAcctValue, Q.DFrom As DFr, Q.DateFromC As DFrC,
Q.DateToC As DToC, DR.DollarFrom
INTO tblRange
FROM tblCust As Q, tblDollarRanges As DR
WHERE (Q.AcctValue>=DR.DollarFrom) And (Q.AcctValue<DR.DollarTo)
GROUP BY Q.MthFrom, Q.MthTitle, DR.DollarTitle, Q.DFrom, Q.DateFromC, Q.DateToC, DR.DollarFrom
UNION ALL SELECT S.MthFrom, S.MthTitle, S.DollarTitle, S.CountOfCustomerNumber, S.SumOfAcctValue, S.DFrom, S.DateFromC,S.DateToC, S.DollarFrom
FROM tblSumm As S
WHERE NOT EXISTS
(SELECT Q2.MthFrom, Q2.MthTitle, DR2.DollarTitle
FROM tblCust As Q2, tblDollarRanges As DR2
WHERE (Q2.AcctValue>=DR2.DollarFrom) And (Q2.AcctValue<DR2.DollarTo)
And S.MthFrom=Q2.MthFrom and S.MthTitle=Q2.MthTitle AND S.DollarTitle=DR2.DollarTitle
)
ORDER BY 1, 3;

 

[RoyVidar]

The easy fix, is to just format the columms in Excel to date.

You say the fields in the temporary table are char(10)? Then it seems there's some implicit conversion going on, either between the table and the report, or I think it's more likely to be between the report and Excel, cause the numbers, are what's actually stored in a date/time field in Access (number of days since 31/12/1899), I think also in SQL-server.

I don't know how to circumvent this, except try
1 - use automation to format the columns after the export, or
2 - export the table in stead of the report (just change the autput to method, using acOutputTable)

Roy-Vidar

 

[RoyVidar]

Looking a bit more closely, one way of circumventing the implicit conversion, would be to not convert the date to char(10), but keep it has datetime. In stead of using the convert to strip of the time part, I think you could use DateAdd/DateDiff, here's sample for the DFrom and DateFromC fields:

[tt]DateAdd(d, DateDiff(d, 0, GetDate() - Day(GetDate())), 0) as DFrom,
DateAdd(d, DateDiff(d, 0, DATEADD(Month, -[MthTo], GETDATE() - Day(GETDATE()) + 1)), 0) as DateFromC,[/tt]

But this is perhaps more an SQL server issue...

Roy-Vidar

 

[earthandfire]

Set the ControlSource of the text boxes to =CDate(FieldName), and change the Name property of the text boxes to someting other than the fieldname:

Textbox
Name: DateFromC ---> DateFromCust
ControlSource: =CDate(DateFromC)

Textbox
Name: DateToC ---> DateToCust
ControlSource: =CDate(DateToC)