filter datagrid using datetimepicker

[mjenkinson05]

Im sorry if this is a really obviously easy question.

I have a msaccess database linked to my VB.NET app.
In it is a table that stores appointments consisting of: ID, Stylist, Customer, Date, Time.
What i have is a form with a datagrid and a query to show just the Date Stylist & Customer, but i cant get it to filter using the datetimepicker, so the user can pick a date using the DTP and then the datagrid will only show items with that date.

hope that makes sence and you can help

 

[cjard]

hmm.. howcome you have a separate column for date and time, did you know that a datetime in most databases (including access) stores the date and time together (it has to; computers dont really understand date and time as being different because they are both just a number of milliseconds since a certain point in time..)

However, given that they are stored separately it might make this part easier..

the datagrid is bound to the data source through a BindingSource.. Simply set the filter of the bindingsource to be:

.Filter = string.Format("[Date] = #{0}#", datetimepicker.Value.ToString("dd-MMM-yyyy"))

 

[mjenkinson05]

Thanks for the help.
I put the filter code into the DateTimePicker1_ValueChanged but when it runs i get this error:
String was not recognized as a valid DateTime.
Ive tried alsorts to fix it, but as a novice i was probably doing more harm than good.
Any help would be appreciated.

 

[cjard]

Sorry, my bad advice..

.Filter = string.Format("[Date] >= #{0}# AND [Date] <= #{0} 23:59:59", datetimepicker.Value.ToString("MM/dd/yyyy"))

Its very annoyingly always american format.. i had hoped that presenting it with a date such as #15-apr-2007# like other microsftware (Access, Excel) can cope with because its unambiguous.. But american numerical format it is then!