compare mysql date to dtpicker date value

[2mhzbrain]

Please show me what is the correct code to compare the DateAdded (2012-12-26 00:00:00 - its format inside MySQL) and DTPicker (01-01-2000 default format from user input)

Sub FilteredQuery()
cnDDR.Open "Driver={Mysql ODBC 3.51 Driver}; server=x; port=3307; database=x; user=x; password=x; OPTION= 1 + 2 + 8 + 32 + 2048 + 16384"

    With cmd
        .ActiveConnection = cnDDR
        .CommandType = adCmdText
        .CommandText = "SELECT ItemCode, ItemName, Category, Description, Supplier, ItemPrice, InStock, OnOrder, DateAdded FROM comparts WHERE Category = '" + CategorySorter.Text + "' AND AND DateAdded >= #" & Format(DTPicker1.Value, "YYYY/MM/DD") & "# AND DateAdded <= #" & Format(DTPicker2.Value, "YYYY/MM/DD") & "#"
        .Execute
    End With

    With rsDDR
        .ActiveConnection = cnDDR
        .CursorLocation = adUseClient
        .Open cmd
    End With

End Sub

[tinstaafl]

instead of Format(DTPicker2.Value, "YYYY/MM/DD") try dateTimePicker1.Value.ToString("u")

[AndreRet]

Mmmm. .ToString is .net code, will not work in vb6.

WHERE Category = '" + CategorySorter.Text + "' AND DateAdded >= DateValue('" & DTPicker1.Value & "') AND DateAdded <= DateValue('" & DTPicker2.Value & "')"

''Remove extra AND...

Rather set up your dtpicker to display your dates via the controls properties. That will ensre that the correct format is returned when clicked on. That let you submit a "clean" sql query to the table.

[2mhzbrain]

i doubled the AND...too frustrated that night ^__^
after long hour of trial and error with reading on MySQL documentation, this works fine, but the question is, is there a conflict using this:

"SELECT DateAdded FROM comparts WHERE Category = '" + CategorySorter.Text + "' AND DateAdded >= '" & Format(DTPicker1.Value, "YYYY/MM/DD") & "' AND DateAdded <= '" & Format(DTPicker2.Value, "YYYY/MM/DD") & "'"

[AndreRet]

No, there should be no conflict. It does open a way for errors though if something is typed incorrectly (we are all human :)). I personally will set my dtPicker to return the correct format.

Happy coding.