Help with generating week dates for a table.

[Cameron]

I am working on building a query that would populate a table with the week
starting date and ending date for each week for every month until 2014.

How might I go about doing this?

Example: For Year 2009, week 1 the start date would be January 1, 2009 and
the end date for that week would be January 4th, 2009. Week 2 would be
January 5th, 2009 until January 11th, 2009. What the query would need to do
is ask for start and end dates (January 1, 2008 to January 14, 2014) and then
build the Year, the Week in the year, the start date for that week, and end
date for that week.

Any idea how I would do that?

 

[Jeff Boyce]

Cameron

If you can build a query that returns those, are you sure you need to build
a table? Why not just refer to the query? If you'll explain more what
having this kind of table would allow you to do, folks here may be able to
offer alternate approaches.

One approach might be to take a look at some of the Format() function
options, and/or check the DateAdd() and/or DateDiff() functions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Cameron]

I have been commissioned with altering a existing database that tracks safety
records. Currnetly, it tracks records on a week by week basis with 53 weeks
comprising a year. But the corporation is moving to a gregoian calendar
system as of january 1st 2009. So the table that currently holds the start
week date and end week date needs to be changed to show the new months.
However, certainly could use use a query to determine the start and end weeks
rather than a table.

I will look at the functions you mentioned and see if I can come up with a
query that will do this. Thank you.