Printable View
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)
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.
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
in your sql.Code:"select * from slog where left(wdate,10) =" & Chr$(35) & FormatDateTime(DTPicker2.Value, vbShortDate) & Chr$(35)
:wave:
Thank you zeezee, that was what I was looking for. Well Done!!!!
All dates have a time component. DateSerial() returns a datetime value with a time component of 0, which is midnight.Quote:
Originally Posted by zeezee
Oh ,thanks Ellis, I didnt know about this. :oQuote:
Originally Posted by Ellis Dee