Selecting "out of office hours" records 2

[one234]

Hello All,

I have a MS Access database which contains maintenance reports form our engineers.

1 of the columns contains the date and the time at which the engineers has entered his/her problem report.

Now I want to create a query which contains the problem reports of last month and which are filled in out of office hours.

The column that contains the date and time looks like: "dd-mm-yyyy h:mm:ss AM/PM".

I'm able to retrieve the records from last month by using: between #1-6-2005# and #30-6-2005#. That is working fine.

So I added the column again and entered: not between #8:00:00 AM# and #5:00:00 PM#. But that didn't work... It still shows all the records in that month

Also I need to add the weekends... Does somebody know how to convert the date to a day???

If somebody can help me with this, it's much appreciated.



Marc D.

If Bill Gates had a nickel for every time Windows crashed... Oh wait, he does...

 

[Remou]

Hi

PHV (MIS)
Between #2005-05-15 02:00:00# And #2005-05-16 04:25:00#

The above quote shows the format for date / time queries. Although your times are AM / PM, this is just a format, nothing to do with how they are stored.
As for weekends:
How to determine the First/Last day of a week
faq701-3103

 

[one234]

So if I understand correct I need to make 30 entries (And change them every month) to filter the time??




Marc D.

If Bill Gates had a nickel for every time Windows crashed... Oh wait, he does...

 

[Remou]

Hi
Sorry, I did not explain myself well, I was trying to say that AM / PM is not needed. I have tried this and it seems to work on a field formatted as "hh:nn:ss"
Not Between #08:00:00# And #17:00:00#

 

[JonFer]

You can extract the time part of a date/time field as a new column in your query and put the time criteria there. You can also use a function to get the day of the week so you can find the weekends. Look up the date/time functions in help for the details.

John

http://www.interautomateconsulting.com

 

[Eupher]

Hi, one234,

Here's some SQL that has all the ingredients. Customize as needed for your purposes:

SELECT Jobs.JobID, Jobs.StartDateTime, Weekday([StartDateTime]) AS MyDay, TimeValue(Format([StartDateTime],"Long Time")) AS MyTime
FROM Jobs
WHERE ((Jobs.StartDateTime) Between #3/1/2003# And #3/31/2003#) AND (Weekday([StartDateTime]) Not In (1,7)) AND (TimeValue(Format([StartDateTime],"Long Time")) Between #8:0:0# And #16:30:0#);

Ken S.

 

[one234]

Eupher,

THANK YOU!!!!! This really helped... This is just what I needed!!!!


Marc D.

If Bill Gates had a nickel for every time Windows crashed... Oh wait, he does...