Find records between dates?

[dakota97]

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

[BlueOysterCult]

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:

   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

[dakota97]

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?

[Mehyar]

Yes you are, although if the field DateIn has a date datatype in the database, you should replace the single quotes by #

 

So

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

 

becomes

 

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

 

Other than that everything is correct ...