Search the database with dates range

Hi.

I want to search the database and find all the records in particular date range. I select dates from DateTimePicker. The appropriate field in the database is in short date format (Access database). I tried to perform this command: "SELECT * FROM MyTable WHERE DateField BETWEEN " + FromDateTimePicker.Value.ToShortDateString() + " AND " + ToDateTimePicker.Value.ToShortDateString(). It looks OK, but it finds no record. If I replace FromDateTimePicker.Value.ToShortDateString() with FromDateTimePicker.Value.Date it causes an exception. Can somebody write me what is the correct command in my case

Thanks.

Answer this question

Search the database with dates range

bpampuch
Thank you very much. It was very helpful.
Alex Cunha
MS Access seperates dates with a #, thats whats causing the error. I'd also recommend you use ToLongDateString as Access frequently gets short date formats wrong i.e. 11/1/05 will be taken as 1st of November, but 13/1/05 will be taken as 13th of January. You'll need to change the string to
... BETWEEN #" +FromDateTimePicker.Value.ToLongDateString() + "# AND ...

The reason for the value and shortdate having different errors is that access sees a value such as 11/1/05 as a sum to calculate if it's not # seperated i.e. 11 divided by 1 divided by 5

Cathal
Search the database with dates range

Answer this question