Date query for extracting records between 8:00 and 5:00 M through Friday of Last week

[cbenisch]

I need to create a query that extracts all records from a table that are between 8:00 AM and 5:00 PM Monday through Friday. I need to create several queries such as last week, Current Month and last month relative to the date it is run. For example if the date is Tuesday April 23, 2013 and I run the query for last week it would be those records Monday Through Friday from 8:00 MA to 5:00 PM each day 4/15/2013 to 4/19/2013. Current month would be every Monday through Friday from 8:00 AM to 5:00 PM from April1, 2013 to April 23, 2013. Last Month would be as the last statement but for the month of March. The record contains a field that is in the Date/Time format. I would appreciate any guidance on how best to go about building this. Thanks

[June7]

Does that mean there are records with times outside that 8 to 5 range? How is value stored - date and time in one field?

[cbenisch]

Yes there are records with times outside the 8 to 5 range and outside the Monday through Friday range. I do not want to include them. It is date and time stored in one field as the Date/Time format. I am actually doing an ODBC connector LINK from Access 2010 to a MySQL database that contains the data.

[June7]

What is start of your week - Monday? If current day is Saturday or Sunday pull the preceding Monday and Friday?

Take last week criteria for example.

You need to use current date and calculate the relevent Monday and Friday dates.

Calculate two fields that extract the date and time parts.

Format([datefield],"mm/dd/yyyy")

Format([datefield], "hhnnss")

Then apply criteria to each of those fields.

Between Date - Weekday(Date, 2)+1 And Date - Weekday(Date, 2) + 5

Between "080000" And "170000"

Problem with using current date is if you run report first thing Monday, do you really want that week or the previous week?

For current month, extract the year/month as well as the day number and time parts.

Year([datefield]) & Month([datefield])

Weekday([datefield], 2)

Apply criteria:

Year(Date()) & Month(Date())

Between 1 and 5

Between "080000" And "170000"

Problem with using current date is if you run report on first day of month, do you really want that month or the previous month?

[cbenisch]

Thank you very much for the input. I will take this and run with it for a while and see where it gets me. In the end I am going to build several queries such as last week, current week, last month, current month, Last quarter and so on. The part I am struggling most with is how to make sure for each query period it is only pulling records Monday through Friday 8 to 5. I want to ignore all other records.

[cbenisch]

I have tried this and I am having a couple of issues. The have a query that pulls records from to table with a join to get all relative data. I added two columns to this query to create the two fields as you suggest. I then tried to use >=Date()-Weekday(Date(),2)+1 And <=Date()-Weekday(Date(),2)+5 on the date filed to try to get the M to F dates and the query returns nothing.

I was using >=(((Date()-Weekday(Date(),2))+1)-7) And <=(((Date()-Weekday(Date(),2))+1)-2) on the original date/time field and this worked to get the Monday through Friday of last week. When I try this same query on the new date field created above it also returns nothing.

If I use this filter on the original date/time field and then use >="08:00:00" And <"17:00:00" on the time field I get the expected results but it is very very slow. Magnitudes slower than the original criteria on the original date/time field by itself. IS this because the original date/time filed from the table is indexed and the new fields in the query are not?

Again thanks for the help

[cbenisch]

This worked and seems fast but is pretty ugly. I am running it against the original date/time field.
(>=(((Date()-Weekday(Date(),2))+1)-7) And <=(((Date()-Weekday(Date(),2))+1)-2)) And ((Format([StartTime],"hh:nn:ss")>="08:00:00") And (Format([StartTime],"hh:nn:ss")<"17:00:00"))

[June7]

A variation on my suggestion but as long as it works ...

[cbenisch]

I would like to understand why yours did not produce the results that we both expected. Can you tell me what I might be doing wrong with what I tried with your suggestions? They appear much cleaner and if I can get them to work without the performance hit would give me more flexibility

[June7]

I was winging it and didn't test. Part of issue is Format parameters I used result in a string, not a date or time value. Either convert the string back to a date with CDate() function or use date-specific parameters:

Format([datefield], "Short Date")


I expect the indexing could be a factor.