comparing dates in ADO

[Guest]

Im trying to compare a date in the where part of the sql. The reson for this
is to pull up a recordset based on the date on the form. Thank you in advance

Here is my code:

Dim cn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim strConnection As String
Dim sqstr As String

Set cn = CurrentProject.Connection

Set RS = New ADODB.Recordset

With RS
Set .ActiveConnection = cn
.Source = "SELECT * FROM cclSchedule WHERE (cclScheduleStart=" &
Me.txtDate & ")"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With

 

[Douglas J Steele]

Dates in Access must be delimited with # characters, and should be in
mm/dd/yyyy format (any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy
will work. dd/mm/yyyy with not, though, at least not for the first 12 days
of each month)

.Source = "SELECT * FROM cclSchedule WHERE (cclScheduleStart=" & _
Format(Me.txtDate, "\#mm\/dd\/yyyy\#")

Other DBMS have other restrictions. For example, SQL Server requires single
quotes around the date, and usually requires yyyy-mm-dd format.

 

[Guest]

Thank you..

The database is on SQL server so, I tried the follow and it errored:
Runtime error, incorrect syntax near '2005-10-10'

The cclScheduleStart field in the table on SQL server is a datetime datatype

Here is how I coded it, am I missing something?

Dim cn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim strConnection As String
Dim sqstr As String

Set cn = CurrentProject.Connection

Set RS = New ADODB.Recordset

With RS
Set .ActiveConnection = cn
.Source = "SELECT * FROM cclSchedule WHERE (cclScheduleStart=" & _
Format(Me.txtDate, "'yyyy-mm-dd'")
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With

Me.Refresh

 

[Douglas J Steele]

While the database may be on SQL Server, you're using a linked table, aren't
you? (At least, that's what the CurrentProject.Connection implies to me).
That means you're still querying using Jet, so try the first solution I
gave.

 

[Guest]

It is an ADP...maybe I have it coded wrong, Im not sure as I am new to the
ADP and ADO.

 

[Dirk Goldgar]

Thank you..

The database is on SQL server so, I tried the follow and it errored:
Runtime error, incorrect syntax near '2005-10-10'

The cclScheduleStart field in the table on SQL server is a datetime
datatype

Here is how I coded it, am I missing something?

Dim cn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim strConnection As String
Dim sqstr As String

Set cn = CurrentProject.Connection

Set RS = New ADODB.Recordset

With RS
Set .ActiveConnection = cn
.Source = "SELECT * FROM cclSchedule WHERE (cclScheduleStart="
& _ Format(Me.txtDate, "'yyyy-mm-dd'")
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With

Me.Refresh

You're missiong a closing parenthesis:

.Source = _
"SELECT * FROM cclSchedule WHERE (cclScheduleStart=" & _
Format(Me.txtDate, "'yyyy-mm-dd'") & _
")"