Query Time from Date/Time Field

[Telobamipada]

I have a table that has a short date field (Field1), and another General Date
field (Field2). Can I set up a query to look between 2 dates using "Field1"
and then ask for times between 10pm and 8am using "Field2"?

My goal was to look at a specific month and see the volume of calls between
10pm and 8am. Is that possible?

I was attempting to limit the date range using "Field1" and using "Field2"
to try pulling times > #10:00:00 PM# And < #8:00:00 AM# with no hits...

Thanks for any help!

 

[Telobamipada]

Right. Field1 is a date field formatted as a short date holding a date only.
Field2 is a date field formatted as general date holding a Date/Time. In this
particular database, there are several date/time fields because they monitor
time differences between several fields in a 24 hour operation. My attempts
at this have not returned any results at all. I thought this would be
something simple where in my query I could set a criteria on Field1 as
"Between #9/1/2007# And #9/30/2007#" and then set a criteria on Field2 as ">
#10:00:00 PM# And < #8:00:00 AM#" which is obviously not working for me.
Perhaps there is a way I can make this happen using Field2 only? I just want
to know for example, during the month of September 2007 how many entries were
made in Field2 which were between the hours of 10pm and 8am. I can do this
for a single day but can make it work over multiple days.
Thanks!

 

[Marshall Barton]

Right. Field1 is a date field formatted as a short date holding a date only.
Field2 is a date field formatted as general date holding a Date/Time. In this
particular database, there are several date/time fields because they monitor
time differences between several fields in a 24 hour operation. My attempts
at this have not returned any results at all. I thought this would be
something simple where in my query I could set a criteria on Field1 as
"Between #9/1/2007# And #9/30/2007#" and then set a criteria on Field2 as ">
#10:00:00 PM# And < #8:00:00 AM#" which is obviously not working for me.
Perhaps there is a way I can make this happen using Field2 only? I just want
to know for example, during the month of September 2007 how many entries were
made in Field2 which were between the hours of 10pm and 8am. I can do this
for a single day but can make it work over multiple days.

Did you try using the TimeValue function in a calculated
field so the time criteria would be effective? If you did,
what was wrong with the result?