comparing dates in ADO

J

Jeff

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
 
D

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.
 
J

Jeff

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
 
D

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.
 
J

Jeff

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

Dirk Goldgar

Jeff said:
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'") & _
")"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top