[SOLVED] Search Between Two dates in MySql Database

Search Between Two dates in MySql Database

I've a problem making a SQL query in vb6 using MySQL database. Basically, what I'm trying to do is retrieve data from the MySQL database into a list view. Although the same code is working perfectly fine with Access database.

I am writing the error that comes:

[MYSQL][ODBC 5.1 Driver] [mysql-5.5.5-10.4.28-MariaDB] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB Server version for the right syntax to use near " at line 1

Dim list As List Item
ListView1.ListItems. Clear
Dim date1 As Date
Dim date2 As Date
date1 = Me. DTPicker1.Value, "mm/dd/yyyy")
date2 = Me. DTPicker2.Value, "mm/dd/yyyy")
If mysqlcn.State = adStateOpen Then mysqlcn.Close

Do Events
Set mysqlcn = New ADODB.Connection
With mysqlcn
.ConnectionString = "Driver= {MySQL ODBC 5.1 Driver};"
& "Server=localhost;"
& "Database=db_test;"
& "User=root;"
& "Password=;"
& "Option=3;"
.CursorLocation = adUseClient
. Open
End With
Rs. Open "Select* from register where Date0 between # " & date1 & " #  AND # " & date2 & " #", mysqlcn,        adOpenDynamic, adLockPessimistic
Do Until rs.EOF
Set list = ListView1.ListItems. Add(, , rs!ID)
list.SubItems(1) = rs!Date0
list.SubItems(2) = rs!CashStatus
list.SubItems(3) = rs!TrxId
list.SubItems(4) = rs!To
list.SubItems(5) = rs!AccountNo
list.SubItems(6) = rs!Amount
list.SubItems(7) = rs!AccountTitle
list.SubItems(8) = rs!From
list.SubItems(9) = rs!PrintedBy
list.SubItems(10) = rs!Comission
rs.MoveNext
Loop
rs.Close

Solution

Rs.Open "Select * from register where Date0 between '" & _
        Format(date1, "yyyy-mm-dd") & "' AND '" & _
        Format(date2, "yyyy-mm-dd") & "'", _
        mysqlcn, adOpenDynamic, adLockPessimistic