Access Database Date Filter not working..

[bigbytes]

Hi guyz im a newbie to VB programming hope u can help me with this.

My problem is that i want to filter my database by date but i guess there is something wrong with my coding. It is not Filtering my database according to the range I set by using 2 DTPicker.

Im using Access as database
Provider = Microsoft.JET.OLEDB.4.0
Operating Sys = XP

'My code:

Option Explicit

Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Dim SQL As String
-------------------------------------------------------------------------
Private Sub cmdExit_Click()
Unload Me
End Sub
-------------------------------------------------------------------------
Private Sub cmdSearch_Click()

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " _
& App.Path & "\database\log.mdb ;Persist Security Info=False"
.CursorLocation = adUseClient
.Open
End With


Set rs = New ADODB.Recordset

DataGrid.Refresh

SQL = "SELECT EM_ID, DATE_LOG FROM TIMELOG WHERE DATE_LOG BETWEEN ' " & DTPicker1 & " ' and ' " & DTPicker2 & " ' "

rs.CursorLocation = adUseClient
rs.Open SQL, cn, adOpenDynamic, adLockBatchOptimistic

Set DataGrid.DataSource = rs

End Sub
-------------------------------------------------------------------------

[VBFnewcomer]

SQL = "SELECT EM_ID, DATE_LOG FROM TIMELOG WHERE DATE_LOG BETWEEN # " & DTPicker1.Value & " # and #" & DTPicker2.Value & " # "

pls note DTP has .Hour, .Minute, .Second, and more. If you are intrested you may use them.
pls read the VB FAQ's/DB FAQ's as to how to pass diff datatype values.

pls use Code Tags while posting code snipptes.

[si_the_geek]

It isn't just the delimiters that matter, you also need to set an appropriate format - as explained in the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)

[zynder]

If you are using Microsoft Access, this will not work.

SQL = "SELECT EM_ID, DATE_LOG FROM TIMELOG WHERE DATE_LOG BETWEEN ' " & DTPicker1 & " ' and ' " & DTPicker2 & " ' "

Replace the ( ' ) with ( # ) when using BETWEEN clause in Microsoft Access.

[bigbytes]

thanks

[zynder]

you are welcome. and also take note that when working with dates and datepicker, you need to make sure that the datatype in the dbase is also date. Don't make it the datatype "text" otherwise you will encounter a problem in the future.

[si_the_geek]

Replace the ( ' ) with ( # ) when using BETWEEN clause in Microsoft Access.

That change is not good enough - the format also needs to be set appropriately, otherwise the value will be interpreted incorrectly at times (especially true if the Regional Settings date format is not US or ISO).

For more information, see the article How can I work with dates correctly? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)