Problem with SQL Statement (from Access) 1

[mikemcginty]

I have an sql statement returning records from three linked Access tables based on a date range and priority. I am porting this across to SQL Server.

The following returns no records but runs ok in Access.
I am new to SQL Server. Can anyone tell me what the problem in the sql statement may be?

SELECT T1.CertificateID, T1.Certificate, T1.RecNumber, T1.fCategoryID, T1.Subject, T1.OwnerName, Min(T2.Priority) AS MinPriority, Max(T3.DateCreated) AS LatestDateCreated
FROM (T1 INNER JOIN T2 ON T1.CertificateID = T2.fCertificateID)
INNER JOIN T3 ON (T2.fCertificateID = T3.fCertificateID) AND (T2.ConfigLevel = T3.lConfigLevelID)
GROUP BY T1.CertificateID, T1.Certificate, T1.RecNumber, T1.fCategoryID, T1.Subject, T1.OwnerName
HAVING Min(T2.Priority) >= 1 AND (max(T3.DateCreated) >= '01/01/30 00:00:00' AND max(T3.DateCreated) <= '07/12/04 23:59:59')
ORDER BY T1.Certificate

Thanks in advance

Mike


When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[indrahig]

the date format is the problem, I guess.
Try to change it into (leave the rest unchanged):

HAVING Min(T2.Priority) >= 1 AND (max(T3.DateCreated) >= '19300101 00:00:00' AND max(T3.DateCreated) <= '20040712 23:59:59')

I Assumed, 01/01/30 is January 1st, 1930 and
07/12/04 is July 12th, 2004. Is it correct??

 

[mikemcginty]

Thanks indrahig

I changed the date format and now get the following error message

Error (-2147217884) Microsoft OLE DB Provider for SQL Server - Rowset does not support fetching backward.

Any ideas?

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[mikemcginty]

Found the problem

It was a small test where I was trying to move around a forward only recordset.

Thanks indrahig

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!