Help needed with select statement with Dates using an Access database

[tterrapin]

I am trying to do a simple select statement where I only select records found with an expiry date >= today. It's an access database and the field is in Date/Time format.

Below are the statements which I have in my asp page but the selection is not working. If I take out the WHERE clause with the date it works fine. It's pretty simple but I must have the syntax wrong.

dim datToday, RS, Conn, SQLQuery
Set Conn = Server.CreateObject("ADODB.Connection&quot
Set RS=Server.CreateObject("ADODB.RecordSet&quot

Conn.open "Manufacturing"

datToday = Date()
SQLQuery = "SELECT * FROM CourseInfo WHERE "
SQLQuery = SQLQuery & "ExpiryDate >='" & datToday & "' ORDER BY COURSE"

The error I'm getting is:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
/Manufacturing/DevelWeb/CourseInfo.asp, line 19

Any help would be greatly appreciated.

Thanks
Cheryl aka Tterrapin

 

[wushutwist]

Try putting hash marks # around the date. Access has some strange SQL syntax. Wushutwist

 

[shaddow]

Try my way...

Set Connection = Server.CreateObject( "ADODB.Connection" )
Connection.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=databasename;Data Source=sqlservername"

Const adOpenStatic = 3
Const adUseClient = 3
Const adLockPessimistic = 2

set rs=server.CreateObject("ADODB.Recordset&quot

sub ExecuteSQL(byval cmd)
if rs.State=1 then rs.Close
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Source = cmd
rs.ActiveConnection = Connection 'The record set needs to know what connection to use.
rs.Open
end sub

datToday = Date()
SQLQuery = "SELECT * FROM CourseInfo WHERE "
SQLQuery = SQLQuery & "ExpiryDate >='" & datToday & "' ORDER BY COURSE"

ExecuteSQL(SQLQuery)
nrrec=rs.RecordCount

after that u should be able to use all methods and properties of the RS
Hope this helps u...

 

[tterrapin]

Thanks Wushutwist

Using the # around the date field worked perfectly.

Tterrapin