Access query problem

Question

Hi,

I have a c# form in which I retrieve data from Access DB.
I have Two DateTimePickers so I could choose data between dates.
My problems is when I try to retreive data between 19/10/2010 and 12/12/2010 it gives me me data beyond 12/12/2010 but when I try to retreive data between 9/12/2010 and 12/12/2010 it works fine.

here's my query:

C# Syntax (Toggle Plain Text)

"select DateId, NiarDesc, ManagerId, RateText, QuantityVal, ActionID From tbl1_order
 where DateValue(DateId) between "'19/10/2010' and '12/12/2010'";
 

"select DateId, NiarDesc, ManagerId, RateText, QuantityVal, ActionID From tbl1_order where DateValue(DateId) between "'19/10/2010' and '12/12/2010'";

  I also tried to do it with getting the dates form the datetimepicker:

"select DateId, NiarDesc, ManagerId, RateText, QuantityVal, ActionID From tbl1_order
 where DateValue(DateId) between "' + datepicker1.Value.Date.ToShortDateString + '" +
" and '" + datePicker2.Value.Date.ToShortDateString +"'";
 

"select DateId, NiarDesc, ManagerId, RateText, QuantityVal, ActionID From tbl1_order where DateValue(DateId) between "' + datepicker1.Value.ToShortDateString + '" + " and '" + datePicker2.Value.ToShortDateString +"'";

It seems that it ignores the month value and considers only the day value.
Why this is happening?

Thanks,
Udi