Dates are all the same

[lunarpc]

Hi,
I'm using access 2003 connecting to SQL 2005 thru ODBC. I've linked the SQL
tables in the access database. I created a SQL view that has a date field.
All the dates are very similar ( 7/1/2008, 7/1/2009, 7/1/2010, ect). When I
link the view in access, the dates are all 7/1/2008. I took that view and
copied the SQL statements and pasted them into an Access SQL Pass-through
query and the dates are as they should be.

I would rather this be a SQL-view rather than an MS Access Pass-through
query. Is there a fix to this problem. (oh, I already tried the SQL convert
and cast, they did not effect this problem). I'm suspecting that this is a
Jet problem.

My SQL statement:
SELECT c.CycleID, s.ScenarioID, p.ProjectAutoID,
SUM(sfti.FutureFundingRequired) AS FutureFunding, SUM(sfti.PreviousFunding)
AS PreviousFunding,
CONVERT(varchar(10), sftid.CostDate, 101) AS CostDate,
SUM(sftid.Amount) AS ScopeTotal
FROM CIPAceDBProd.dbo.Cycles AS c INNER JOIN
CIPAceDBProd.dbo.Projects AS p ON c.CycleID =
p.CycleID INNER JOIN
CIPAceDBProd.dbo.Scenarios AS s ON c.CycleID =
s.CycleID INNER JOIN
CIPAceDBProd.dbo.ScopingProjectAreaScenarios AS spas
INNER JOIN
CIPAceDBProd.dbo.ScopingProjectAreas AS spa ON
spas.ScopingProjectAreaID = spa.ScopingProjectAreaID INNER JOIN
CIPAceDBProd.dbo.ScopingProjectScenarios AS sps ON
spas.ScopingProjectScenarioID = sps.ScopingProjectScenarioID INNER JOIN
CIPAceDBProd.dbo.ScopingStatus AS ss ON
spas.ScopingStatusID = ss.ScopingStatusID INNER JOIN
CIPAceDBProd.dbo.ScopingFinancialResults AS sfr ON
spas.ScopingProjectAreaScenarioID = sfr.ScopingProjectAreaScenarioID INNER
JOIN
CIPAceDBProd.dbo.ScopingFinancialTypeItems AS sfti
INNER JOIN
CIPAceDBProd.dbo.ScopingFinancialTypeItemDetails AS
sftid ON sfti.ScopingFinancialTypeItemID = sftid.ScopingFinancialTypeItemID
INNER JOIN
CIPAceDBProd.dbo.ScopingFinancialTypeResults AS sftr
ON sfti.ScopingFinancialTypeResultID = sftr.ScopingFinancialTypeResultID ON
sfr.ScopingFinancialResultID =
sftr.ScopingFinancialResultID ON p.ProjectAutoID = spa.RecordAutoID
WHERE (sps.IsCurrent = 1) AND (c.IsDefault = 1) AND (s.IsDefault = 1)
GROUP BY c.CycleID, s.ScenarioID, p.ProjectAutoID,
sfti.FutureFundingRequired, sfti.PreviousFunding, sftid.CostDate

 

[louisjohnphillips]

Hi,
I'm using access 2003 connecting to SQL 2005 thru ODBC. I've linked the SQL
tables in the access database. I created a SQL view that has a date field..
All the dates are very similar ( 7/1/2008, 7/1/2009, 7/1/2010, ect). WhenI
link the view in access, the dates are all 7/1/2008. I took that view and
copied the SQL statements and pasted them into an Access SQL Pass-through
query and the dates are as they should be.

I would rather this be a SQL-view rather than an MS Access Pass-through
query. Is there a fix to this problem. (oh, I already tried the SQL convert
and cast, they did not effect this problem). I'm suspecting that this is a
Jet problem.

My SQL statement:
SELECT     c.CycleID, s.ScenarioID, p.ProjectAutoID,
SUM(sfti.FutureFundingRequired) AS FutureFunding, SUM(sfti.PreviousFunding)
AS PreviousFunding,
                      CONVERT(varchar(10), sftid.CostDate, 101) AS CostDate,
SUM(sftid.Amount) AS ScopeTotal
FROM         CIPAceDBProd.dbo.Cycles AS c INNER JOIN
                      CIPAceDBProd.dbo.Projects AS p ON c.CycleID =
p.CycleID INNER JOIN
                      CIPAceDBProd.dbo.Scenarios ASs ON c.CycleID =
s.CycleID INNER JOIN
                      CIPAceDBProd.dbo.ScopingProjectAreaScenarios AS spas
INNER JOIN
                      CIPAceDBProd.dbo.ScopingProjectAreas AS spa ON
spas.ScopingProjectAreaID = spa.ScopingProjectAreaID INNER JOIN
                      CIPAceDBProd.dbo.ScopingProjectScenarios AS sps ON
spas.ScopingProjectScenarioID = sps.ScopingProjectScenarioID INNER JOIN
                      CIPAceDBProd.dbo.ScopingStatus AS ss ON
spas.ScopingStatusID = ss.ScopingStatusID INNER JOIN
                      CIPAceDBProd.dbo.ScopingFinancialResults AS sfr ON
spas.ScopingProjectAreaScenarioID = sfr.ScopingProjectAreaScenarioID INNER
JOIN
                      CIPAceDBProd.dbo.ScopingFinancialTypeItems AS sfti
INNER JOIN
                      CIPAceDBProd.dbo.ScopingFinancialTypeItemDetails AS
sftid ON sfti.ScopingFinancialTypeItemID = sftid.ScopingFinancialTypeItemID
INNER JOIN
                      CIPAceDBProd.dbo.ScopingFinancialTypeResults AS sftr
ON sfti.ScopingFinancialTypeResultID = sftr.ScopingFinancialTypeResultID ON
                      sfr.ScopingFinancialResultID =
sftr.ScopingFinancialResultID ON p.ProjectAutoID = spa.RecordAutoID
WHERE     (sps.IsCurrent = 1) AND (c.IsDefault = 1) AND (s.IsDefault = 1)
GROUP BY c.CycleID, s.ScenarioID, p.ProjectAutoID,
sfti.FutureFundingRequired, sfti.PreviousFunding, sftid.CostDate

Your application may expect a datetime value but receives a varchar.
Thus, it is confused.

Please consider creating a view using your SQL statement but running
in SQL Server.

Do not try to convert the DateTime values to varchar strings.

A view is created using SQL Server Management Studio. Choose New
Query and write in the editor window:

Create
view MyView
as

Your SQL statement follows--but remember, no formatting of the data.

Use the GetExternalData menu option to create a link from your ODBC
datasource and table to your Access table.

Let Access do the date formatting.

 

[lunarpc]

Yeah, thats what I did to begin with which is how I found out that all the
dates came thru as the same date. I only used the convert to try to fool
access but it didn't work. I keep getting 7/1/2008 in that field no matter
what the date is.