Date Range Search

[dglienna]

Here's a method that works. It used the TIME PART of the DATE, which you may not need,

  Select * From Table1 Where Day>Table1.Day AND Day<=Table1.Day + 1

Will also work, and save some time...

Option Explicit



Private Sub Form_Load()
Dim dteDate     As Date
Dim adoRec      As ADODB.Recordset
    dteDate = CDate("7 March 2005 10:04:00AM")
    Set adoRec = GetRecordset(dteDate)
    With adoRec
        Do While Not .EOF
            'your code goes here
             MsgBox .Fields(0)
            .MoveNext
        Loop
        .Close
    End With
    Set adoRec = Nothing
End Sub

Private Function GetRecordset(ByVal pdteSearchDate As Date) As ADODB.Recordset
Dim strSQL      As String
Dim adoRec      As ADODB.Recordset
    strSQL = "SELECT * "
    strSQL = strSQL & "FROM AS_Createsched "
    strSQL = strSQL & "WHERE AS_Createsched.CSIN_dates = #" & Format$(pdteSearchDate, "d mmm yyyy") & "# "
    strSQL = strSQL & "AND HOUR(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "hh") & " "
    strSQL = strSQL & "AND MINUTE(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "nn") & " "
    Set adoRec = New ADODB.Recordset
    adoRec.Open strSQL, GetConnString, adOpenForwardOnly, adLockReadOnly
    Set GetRecordset = adoRec
    Set adoRec = Nothing
End Function

Private Function GetConnString() As String
    GetConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db4.mdb;Persist Security Info=False"
End Function