Find records between dates?

D

dakota97

Centurion
Joined
Nov 14, 2003
Messages
116
Location
Pennsylvania
Hi all,

I have an Access DB that has the fields EmpID, DateIn, StartTime and EndTime. I need to retrieve all records from the DB that have a value in their DateIn field that are between 2 specific dates that the user selects from a datepicker control on my form. How can I do this? I've tried searching the forums, but can't really find anything on this topic. Any suggestions will help.

Thanks in advnace,

Chris
 
The user is picking the dates? I have a similar menu item on in a program I am doing...
I have a Cd Colletiong that the user can "filter" by year title etc they choose this via a menu item By Year
it is something like:
Code: 
    Private Sub mnuByYear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuByYear.Click
        Dim strSQL, strYear As String
        strYear = InputBox("Please enter a year to search" & vbNewLine & "Please use the format: 1984-1987")
        strYear = strYear.Replace(" ", "") 'removes the spaces from the input
        Dim ArrYear() As String = strYear.Split("-"c) ' this would divide the string based on the - so you will have two resulting strings 
        '(which are the two years inputted by the user)
        'Now ArrYear(0) contains the first year
        'ArrYear(1) contains the second year
        strSQL = "select CdArtist,CdTitle, CdYear,CdKey,CdCategory from Cds where CdYear between " & ArrYear(0) & " and " & ArrYear(1) 'ORDER BY CdYear
        OleDbSelectCommand1.CommandText = strSQL
        DbCdCollection1.Clear()
        OleDbDataAdapter1.Fill(DbCdCollection1)
        UpdateCounter()

    End Sub

is this what you mean?
(some credit going to Mehyar)


Rob
 
That's the right concept, but not quite how I'd like to do it. I've played around with some code, and have accomplished to convert the datepicker value to match the value of the field in my DB with this code:


Dim strdate As String = DateTimePicker1.Value.ToShortDateString
Dim enddate As String = DateTimePicker2.Value.ToShortDateString


Now what I need to do is find all of the records that have a DateIn value between the seleted dates. My idea was coding it something like the following, although it's not working:



'Create the database connection
Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb")
MyConnection.Open()

'Query the database to find matching record
Dim MyCommand As New OleDbCommand("SELECT * FROM Timein WHERE DateIn between '" & strdate & "' and '" & enddate & "'", MyConnection)

'Declare a variable to read the database information
Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader()


Am I at least on the right track?
 
Yes you are, although if the field DateIn has a date datatype in the database, you should replace the single quotes by #

So
Visual Basic: 
Dim MyCommand As New OleDbCommand("SELECT * FROM Timein WHERE DateIn between '" & strdate & "' and '" & enddate & "'", MyConnection)

becomes

Visual Basic: 
Dim MyCommand As New OleDbCommand("SELECT * FROM Timein WHERE DateIn between #" & strdate & "# and #" & enddate & "#", MyConnection)

Other than that everything is correct ...
 
Back
Top