[RESOLVED] Searching an Access Date/Time field?

[artstar]

Good Morning.
I am having trouble searching an Access(2000) DataTime Field.

The field holds both the Date and Time: But I only want to search this field by the Date.

Below is my search string it returns no records. (Does not give an error, just no records.)

searchsql = "select * from slog where wdate=" & Chr$(35) & FormatDateTime(DTPicker2.Value, vbShortDate) & Chr$(35)

[Ellis Dee]

Try searching a range:

searchsql = "select * from slog where wdate >= #" & _
DateSerial(Year(wdate), Month(wdate), Day(wdate)) & _
"# And wdate < #" & _
DateSerial(Year(wdate), Month(wdate), Day(wdate) + 1) & "#;"

Note that DateSerial() handles rollover automagically, so you can simply add 1 to the day without worrying about if it's the last day of the month.

DateSerial() is better for this than DateAdd() because DateAdd() would retain the time information, thus returning undesired results.

Of interest is that the first term needs to be ">=" the date itself, instead of ">" the day before. That's because a date without a time component resolves to midnight at the beginning of that day.

[zeezee]

I m little bit confused, date serial returns , a date, but that is compared to a Date Time Field.
So even ith Ellis Dee's suggestion , the result wont come right?

try like

"select * from slog where left(wdate,10) =" & Chr$(35) & FormatDateTime(DTPicker2.Value, vbShortDate) & Chr$(35)

in your sql.

[artstar]

Thank you zeezee, that was what I was looking for. Well Done!!!!

[Ellis Dee]

I m little bit confused, date serial returns , a date, but that is compared to a Date Time Field.
So even ith Ellis Dee's suggestion , the result wont come right?

All dates have a time component. DateSerial() returns a datetime value with a time component of 0, which is midnight.

[zeezee]

All dates have a time component. DateSerial() returns a datetime value with a time component of 0, which is midnight.

Oh ,thanks Ellis, I didnt know about this.