How to get data between two dates using DateTimePicker?

Question

Hiii

Seems it's too common and million times asked on the net and lots of answers are there but none works for me. so, please take a look.

 

>Windows Application using VS2008

>Access Database. Data Type = Date/Time, Format = Short Date.

 

Two DateTimePickers and a button's click event. I just want to get records between two dates INCLUDING the dates in the DTP.

I have searched and found different select statements to fetch data based on two dates.

1. OleDbDataAdapter FetchBillList = new OleDbDataAdapter("Select BillSrNo, BillPatCase, BillPatName, BillAmtPaid From Billings Where BillDate Between'" + dtpFromDt.Value + "' AND '" + dtpTodt.Value + "'", MyConnAcc);

//Error - Data type mismatch in criteria expression.

 

2.OleDbDataAdapter FetchBillList = new OleDbDataAdapter("Select BillSrNo, BillPatCase, BillPatName, BillAmtPaid From Billings Where BillDate >='" + dtpFromDt.Value.ToShortDateString() + "' AND BillDate<='" + dtpTodt.Value.ToShortDateString() + "'", MyConnAcc);

//Error - Data type mismatch in criteria expression.

 

3.OleDbDataAdapter FetchBillList = new OleDbDataAdapter("Select BillSrNo, BillPatCase, BillPatName, BillAmtPaid From Billings Where BillDate Between#" + dtpFromDt.Value.ToShortDateString() + "# AND #" + dtpTodt.Value.ToShortDateString() + "#", MyConnAcc);

//This query does not show any error but also not fetching proper data. It gives all the records regardless mentioned in the DTP.

 

4.Also used parameterized query but no result.

 

 I have searched on net but no answer matches my requirement.

 

Please help.

 

Mayank