Filter Query based on Date and Time

[kobiashi]

I have a query with a number of fields and i want to filter the data based on a date and time filter.

the filter needs to be between 02:00 today and 02:00 yesterday

there is a Date field and a time field


do i have to filter each field separately ie date field "between Date() and Date()-1"

then apply a filter to the time field

ore do i need to concatenate the two fields first?

 

[Ranman256]

Date and time should be in 1 field: txtStartDate.
Make another text box,txtEndDate: =dateAdd("h",-24,txtStartDate)

SQL:
...between forms!myForm!txtStartDate and forms!myForm!txtEndDate

 

[kobiashi]

hi thanks for the reply


this is not really an option to apply a second field.

there must be a way of filtering the data in the query without this second field

 

[JHB]

..
ore do i need to concatenate the two fields first?

The short answer is YES.

 

[kobiashi]

The short answer is YES.

thanks

any chance for the long answer

how would the query go?

 

[JHB]

Yes of course, I thought you knew the answer since you asked and just wanted a confirmation - database attached, run the query.

 

[MajP]

SELECT tbldatetime.datefield, 
       tbldatetime.timefield 
FROM   tbldatetime 
WHERE  Int([datefield]) + Timevalue([timefield])  BETWEEN 
                  Date() + Timeserial(14, 0, 0) AND 
                  Date() + Timeserial(14, 0, 0) - 1

14:00 today to 14:00 yesterday

 

[MajP]

Change 14 to 2 if that is AM.

You may ask why I take the int of the date field, and the timevalue of the time field. This is to ensure that the date field does not have some hidden time part disguised by formatting and the time field does not have hidden date value disguised by formatting. In every date field (datetime field) what you see is only formatting. The current date time is stored in the db as 43341.0289351852
?cdbl(now)
43341.0289351852
?format(43341.0289351852,"mm/dd/yyyy hh:mm:ss")
08/29/2018 00:41:40