Display 4 weeks of data

[A McKay]

Hello all,

I am trying to create a query in which data is displayed for the current
month beginning with the first day of the week, first week of the month and
ending with the last day of the week, last week of the month.

In example: Today is Friday June 1, 2007 but the first day of the week would
be Sunday, May 27, 2007. I would like the query to show data between
5/27/2007 through 6/30/2007 -OR- 4/29/2007 through 6/2/2007 for the previous
month...

Does anyone have any ideas?

 

[Douglas J. Steele]

First day of the current month is DateSerial(Year(Date()), Month(Date()), 1)

Last day of the current month is DateSerial(Year(Date()), Month(Date()) + 1,
0)

Sunday of the current week is DateAdd("d", 1 - Weekday(Date()), Date()),
which is the same as Date() - Weekday(Date()) + 1

 

[John W. Vinson]

Hello all,

I am trying to create a query in which data is displayed for the current
month beginning with the first day of the week, first week of the month and
ending with the last day of the week, last week of the month.

In example: Today is Friday June 1, 2007 but the first day of the week would
be Sunday, May 27, 2007. I would like the query to show data between
5/27/2007 through 6/30/2007 -OR- 4/29/2007 through 6/2/2007 for the previous
month...

Does anyone have any ideas?

Use a criterion of

= DateAdd("d", 1-Weekday(DateSerial(Year(Date()), Month(Date()), 1)), DateSerial(Year(Date()), Month(Date()), 1))

AND < DateAdd("d", 1-Weekday(DateSerial(Year(Date()), Month(Date())+1, 1)),
DateSerial(Year(Date()), Month(Date())+1, 1))


John W. Vinson [MVP]

 

[A McKay]

Doug thank you for such a quick response. What would Sunday of the first
week of the month be? Or is there a way of combining the query criteria you
listed below? Thanks again...

 

[Michael Gramelspacher]

Hello all,

I am trying to create a query in which data is displayed for the current
month beginning with the first day of the week, first week of the month and
ending with the last day of the week, last week of the month.

In example: Today is Friday June 1, 2007 but the first day of the week would
be Sunday, May 27, 2007. I would like the query to show data between
5/27/2007 through 6/30/2007 -OR- 4/29/2007 through 6/2/2007 for the previous
month...

Does anyone have any ideas?

If I understand the problem, you want to retrieve records
between the first day of the week that contains the first day
of the month and the last day of the week that contains
the last day of the month.

This is not pretty, but it seems to work. Test it using the
Northwind sample database.

SELECT Orders.OrderDate,
SUM([Order Subtotals].Subtotal) AS [Daily Sales]
FROM Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.OrderDate
BETWEEN DATEADD("ww",DATEDIFF("ww",1,[Enter 1st of month:]), 1)
AND DATEADD("ww",DATEDIFF("ww",1,DATEADD("m",
DATEDIFF("m",1,[Enter 1st of month:]),1)),1) + 6
GROUP BY Orders.OrderDate;

 

[A McKay]

John, thanks for your quick response too. I worked your criterion and it
worked, however it stopped at the friday of the last whole week. For
example, it stopped at Friday, July 28 instead of August 4th. I was able to
adjust the criterion to give me to the last day of the month which will work
just fine unless you can come up with an idea to include the last incomplete
week...

Thanks again for your help

 

[John W. Vinson]

John, thanks for your quick response too. I worked your criterion and it
worked, however it stopped at the friday of the last whole week. For
example, it stopped at Friday, July 28 instead of August 4th. I was able to
adjust the criterion to give me to the last day of the month which will work
just fine unless you can come up with an idea to include the last incomplete
week...

Note that some months have FIVE weeks not four. Try

= DateAdd("d", 1-Weekday(DateSerial(Year(Date()), Month(Date()), 1)), DateSerial(Year(Date()), Month(Date()), 1))

AND < DateAdd("d", 8-Weekday(DateSerial(Year(Date()), Month(Date())+1, 1)),
DateSerial(Year(Date()), Month(Date())+1, 1))

changing a 1 to an 8 in the end of month criterion.

John W. Vinson [MVP]

 

[Douglas J. Steele]

Sunday preceding first day of current month:

DateAdd("d", 1 - Weekday(DateSerial(Year(Date()), Month(Date()), 1)),
DateSerial(Year(Date()), Month(Date()), 1))

 

[A McKay]

Thanks again for your help

Sunday preceding first day of current month:

DateAdd("d", 1 - Weekday(DateSerial(Year(Date()), Month(Date()), 1)),
DateSerial(Year(Date()), Month(Date()), 1))

 

[A McKay]

Thanks again for your help

Note that some months have FIVE weeks not four. Try

AND < DateAdd("d", 8-Weekday(DateSerial(Year(Date()), Month(Date())+1, 1)),
DateSerial(Year(Date()), Month(Date())+1, 1))

changing a 1 to an 8 in the end of month criterion.

John W. Vinson [MVP]

 

[A McKay]

Thanks for your help

Hello all,

I am trying to create a query in which data is displayed for the current
month beginning with the first day of the week, first week of the month and
ending with the last day of the week, last week of the month.

In example: Today is Friday June 1, 2007 but the first day of the week would
be Sunday, May 27, 2007. I would like the query to show data between
5/27/2007 through 6/30/2007 -OR- 4/29/2007 through 6/2/2007 for the previous
month...

Does anyone have any ideas?

If I understand the problem, you want to retrieve records
between the first day of the week that contains the first day
of the month and the last day of the week that contains
the last day of the month.

This is not pretty, but it seems to work. Test it using the
Northwind sample database.

SELECT Orders.OrderDate,
SUM([Order Subtotals].Subtotal) AS [Daily Sales]
FROM Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.OrderDate
BETWEEN DATEADD("ww",DATEDIFF("ww",1,[Enter 1st of month:]), 1)
AND DATEADD("ww",DATEDIFF("ww",1,DATEADD("m",
DATEDIFF("m",1,[Enter 1st of month:]),1)),1) + 6
GROUP BY Orders.OrderDate;