List of week start and end dates for a given year 3

[BigRed1212]

Surely this is a path well trod, just unfamiliar to me.

Given a year (eg 2010, 2011, 2012 - user input), I would like to return a recordset with week, start of the week, end of the week.

Looking like:

weeknumber  startdate  enddate  
1 1/1/2010  1/2/2010
2 1/3/2010  1/9/2010
3 1/10/2010 1/16/2020
4 1/17/2010 1/23/2010
5 1/24/2010 1/30/2010
6 1/31/2020 2/06/2010
7 2/7/2010 2/13/2010
etc

I've Googled and messed with datepart, datediff, and dateadd but I just don't see it.

Ultimately, I would want to left join this list to a query that returns some counts and stuff grouped by week number, so the final could be used to show what happened between 7/18/2010 and 7/24/2010 (Week 30) for example.

Hints, helps, ideas?

 

[Andrzejek]

How about:

Dim intYear As Integer
Dim datStart As Date
Dim datEnd As Date
Dim i As Integer

intYear = 2010
[green]
'Find First Week[/green]
For i = 1 To 7
    If vbSaturday = Weekday(CDate("1/" & i & "/" & intYear)) Then
        datEnd = CDate("1/" & i & "/" & intYear)
        Debug.Print "Week 1  1/1/" & intYear & " " & datEnd
        Exit For
    End If
Next i

Do While Year(datEnd) < intYear + 1
    datStart = DateAdd("d", 1, datEnd)
    datEnd = DateAdd("d", 6, datStart)
    Debug.Print "Week " & i & "  " & datStart & "  " & datEnd
Loop

You get:
[tt]
Week 1 1/1/2010 1/2/2010
Week 2 1/3/2010 1/9/2010
Week 2 1/10/2010 1/16/2010
Week 2 1/17/2010 1/23/2010
Week 2 1/24/2010 1/30/2010
Week 2 1/31/2010 2/6/2010
Week 2 2/7/2010 2/13/2010
....
Week 2 11/28/2010 12/4/2010
Week 2 12/5/2010 12/11/2010
Week 2 12/12/2010 12/18/2010
Week 2 12/19/2010 12/25/2010
Week 2 12/26/2010 1/1/2011
[/tt]

Have fun.

---- Andy

 

[PWise]

While this can be done with a numbers table and create a recordset on the fly it will be much eaier to create a table wit dates

 

[Andrzejek]

Oops, try:

Do While Year(datEnd) < intYear + 1
    datStart = DateAdd("d", 1, datEnd)
    datEnd = DateAdd("d", 6, datStart)
    Debug.Print "Week " & i & "  " & datStart & "  " & 
datEnd[blue]
    i = i + 1[/blue]
Loop

Numbers of the Week fix

Have fun.

---- Andy

 

[MajP]

Taking Pwise's advice and stealing Andy's code, you probably want to write to a table. AKAIK there is no way to join a recordset to a query. You would basically have to write a lot of code to create a second recordset.

Public Sub fillDates(intYear As Integer)
  Dim datStart As Date
  Dim datEnd As Date
  Dim i As Integer
  Dim strSql As String
  strSql = "Delete * from TblDates"
  CurrentDb.Execute strSql
 'Find First Week
  For i = 1 To 7
    If vbSaturday = Weekday(CDate("1/" & i & "/" & intYear)) Then
        datEnd = CDate("1/" & i & "/" & intYear)
        Debug.Print "Week 1  1/1/" & intYear & " " & datEnd
        strSql = "Insert into tblDates (weekNumber, startDate, endDate) values (1,"
        strSql = strSql & "#1/1/" & intYear & "# , #"
        strSql = strSql & Format(datEnd, "mm/dd/yyyy") & "#)"
        Debug.Print strSql
        CurrentDb.Execute strSql
        Exit For
    End If
  Next i
    i = 2
  Do While Year(datEnd) < intYear + 1
    datStart = DateAdd("d", 1, datEnd)
    datEnd = DateAdd("d", 6, datStart)
    strSql = "Insert into tblDates (weekNumber, startDate, endDate) values ("
    strSql = strSql & i & ", #" & Format(datStart, "mm/dd/yyyy") & "#, #"
    strSql = strSql & Format(datEnd, "mm/dd/yyyy") & "#)"
    Debug.Print strSql
    CurrentDb.Execute strSql
    i = i + 1
  Loop
End Sub

 

[BigRed1212]

Okay. I understand SQL better than I do Access.

I really don't know how to implement what you have written MajP.

I think I have correctly made a module with the code (just pasted it) but I don't know how to invoke or call it. Or pass it the year I want.

I'm Googling but I'm stupid. This is something I should know how to do and I just don't.

 

[BigRed1212]

While this can be done with a numbers table and create a recordset on the fly it will be much eaier to create a table with dates"

And as common a task as this surely is one might even think such a table would be found ready-made on the Web.

I must not have the correct keywords/search terms.

 

[MajP]

I assume you have a form with a text box "txtBoxYear", and a OK button "cmdOK". On the button click event make a procedure. Something like

Public sub cmdOK_click()
if isnumeric(me.txtBoxYear) then
call fillDates(me.txtBoxYear)
end if
end sub

 

[Andrzejek]

MajP's code assumes that you do have a table in your data base called tblDates with fields:[tt]
weekNumber as Number
startDate as Date
endDate as Date[/tt]
and the code [tt]Call fillDates(me.txtBoxYear)[/tt] is called only once per given year, otherwise you will end up with multiple entries for the same year. Unless you Delete all the records for a given year before you Insert any information for that year.


Have fun.

---- Andy

 

[MajP]

Unless you Delete all the records for a given year before you Insert any information for that year

Took that in to consideration

....
  Dim i As Integer
  Dim strSql As String
  [b]strSql = "Delete * from TblDates"
  CurrentDb.Execute strSql[/b]
 'Find First Week

Clears the table each time.

But yes I assumed the table was already made. Forgot to mention that.

 

[PWise]

use a week table
fields
weekyear int
weekno int
WeekStart date
weekend date
week year ,weekno, weekStart ,weekend
2011 1 1/1/11 1/1/11
2011 2 1/2/11 1/8/11
.....


if you have a dates table
1/1/09
1/2/9
......
12/30/09
12/31/09
1/1/10
1/2/10
....
12/30/10
12/31/10
1/1/11
1/2/11
....
12/30/11
12/31/11

create this query

Select year(datefield)as weekyear,Datepart(ww,datefield) as weekno,
min(datefield) as weekstart,max(Datefield)as weekend
from datetable
group by year(datefield),Datepart(ww,datefield)

 

[MichaelRed]

... but, if you are going t create the table, why not include most of the date relevant fields which you / your organization use?

date
IsWeekDay
IsHoliday
Year (for multiyear situations)
FiscalYeay (for those using this)
Quarter
Month
MonthName
MonthDay (1 thru 28 |29 |30 |31)
WeekDay (1 through 7)
WeekDayName
YearWeek (week within the Year)
MonthWeek (WeekNumber in the Month)
...
[Code]


MichaelRed

 

[dhookom]

I like coding and I like SQL even more. However, I would consider just building this table with all values in Excel and then copy and paste (or import) into Access.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

MichaelRed, creating table you suggesting is redundant and should be avoided at all cost, most of the fields should be calculated based on your first field: Date

Dim datDate As Date

datDate = CDate("5/5/2010")

Debug.Print "The name of the month is " & MonthName(Month(datDate))
Debug.Print "The name of the day is " & WeekdayName(Day(datDate))
Debug.Print "The Year is " & Year(datDate)
etc.

gives you
[tt]
The name of the month is May
The name of the day is Thursday
The Year is 2010
etc.
[/tt]

Have fun.

---- Andy

 

[MajP]

Sure you can calculate most of those, and they are expensive calculations. I agree with Michael, if you are going to build a reference table then you might as well spend the time up front and speed up and simplify your queries in the long run.

For what reason would you "avoid at all costs"? That is kind of alarmist.

 

[Andrzejek]

That may be just my approach – having more data in your data base that you really need. It comes from the approach NOT to have data in the table that could be calculated ‘on the fly’, like FieldA, FieldB and FieldC where FieldC = FieldA + FieldB. Such Sum should be calculated (IMHO) not in the data base, but in the code, or – better yet - as a calculated field in the recordset.

But, it could be just me…..


Have fun.

---- Andy

 

[dhookom]

I'm a big believer in not storing data that can be calculated but there is a major difference between [blue]"FieldA, FieldB and FieldC where FieldC = FieldA + FieldB [/blue]and month names and numbers. FieldA and FieldB are probably dynamic and will possibly change over time requiring recalculation. The month names and numbers are static.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

I assume you have a form with a text box "txtBoxYear", and a OK button "cmdOK"."

Gimme a minute. Okay. I do now.

"On the button click event make a procedure."

Okay. Did that.

"MajP's code assumes that you do have a table in your data base called tblDates with fields:"

Okay. Made one.


Hit the button. Found out you can't save the module with the same name as the sub. Remade. Hit the button. It works! Awesome. Thank you.