Querying date and time

[Martin Dempsey]

hi, I'm struggling with a query that I think should be easy, hoping to get some help. I have a table with a date/time field. I need to query this field for a specific date and all records <= 9aM AND all records >= 9PM. When I use the following criteria it works, >=#03/26/2018# and <=#03/26/2018 09:00:00 AM# or >=#03/26/2018 09:00:00 PM# and #03/27/2018#. What I want to do is get away from this hard coding of the date portion and select a field name Text111 on my form named frm_form_update.

Any help will be truly appreciated.

thanks
Martin

 

[SkipVought]

Hi,

???

Date/Time is integral. You state...

I need to query this field for a specific date and all records <= 9aM AND all records >= 9PM.

But this...

What I want to do is get away from this hard coding of the date portion

…is like saying, “Why not dry water.” Or “Why can’t we have a married bachelor?”

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Martin Dempsey]

thanks, that would explain the struggle. I can query a different date field just for the date, I need to figure out how to query the specific time need then. Tried using the format method to format the date field to show just the long time, but can't seem to query that either. I added a field named Time_2:format([derived_datetime_created],"long time") and tried to query using the criteria >=#9:00:00 PM# but that didn't work either, presuming for the same reason you mentioned above?

 

[Martin Dempsey]

all, thanks for the guidance, I found a solution. turned the time portion into a number and querying as >2100


Thanks!

 

[SkipVought]

???

Do you really have a field that contains Date & Time?

If so, 9pm is not the number 2100 in that field.

9pm is 21/24 or 0.875. But it is part of a Date.

Or do you have a separate field for Time and if so, what is the data type?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Martin Dempsey]

I do. I formatted the field as "HHNN" then converted it as a number field. this way I got 9:00:00 PM as 2100. I was then able to filter all records greater then 2100. Works perfectly.