Query Time from Date/Time Field

[Guest]

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!

 

[Marshall Barton]

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...

You should describe the result of what you tried. It might
provide important clues towards a solution.

Short Date and General Date are formats that has nothing to
do with what's actually in the fields. The important thing
is the field data type, which I assume is Date.

If the field3 has a date part along with the time, then I
think you need to cange field2 in the query to a calculated
field:

CallTime: TimeValue(Field2)

 

[Guest]

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?