.NET query of Access database misinterprets dd/mm/yyyy date literal as mm/dd/yyyy

Question

Please help me understanding what is happening

I have a visual basic app that returns data from an access database i want to query a db field with dates with this format "01/12/2015" (Dec 1st) to do so i have a datetimepicker

I have this record in my DB - 01/12/2015

when i update my code to include it datetimepicker value into a query it won't return nothing, if i put in 12/01/2015 it finds the record with 01/12/2015

my query looks like this:

dim dd as date 
dd = datetimepicker.value
qrytxt = "select * from table where [Date] = #" + dd + "#"

if i put a msgbox to return qrytxt it returns

select * from table where [Date] = #01/12/2015#

but it still checks the wrong date against the db...

i also have this Imports System.Globalization and i read something about a CultureInfo but i couldn't put it to work in my query.

Is there anything else i need to include or do ?

Answer 1

Just force a format on your date value to create a string expression for the date:

Dim dd As Date 
dd = datetimepicker.value
qrytxt = "select * from table where [Date] = #" + dd.ToString("yyyy'/'MM'/'dd") + "#"