Find each Monday in the year 2

[lbigk]

Hello,

I need to get a Date for each Monday in the year. I have 2 variables Year and Week, so at the end what each record should have is:

2008 1 Jan-7
2008 2 Jan-14

Thank you.

 

[JBinQLD]

While You could use datepart function a quick and dirty for this would be to start from a known and just increment by 7!

dim db as database
dim rs as recordset
dim intCounter as integer
dim dteMonday as date

set db=currentdb
set rs=db.openrecordset("tblYourTable",dbopendynaset)

dteMonday=#7/01/2008#
for intCounter=1 to 52 
  rs.addnew
     rs!Year="2008"
     rs!Week=intCounter
     rs!MondayDate=dteFirstmonday
  rs.update
  dteMonday=dateadd("d",7,dteMonday)
next intCounter
rs.close
 
set rs=nothing
set db=nothing
msgbox("Table populated")

hope this helps,

JB

 

[Remou]

Note that year and week number can be got with a format, so there is no reason to store anything other than the date.

Sub CreateMondays()
Dim dteMon As Date
Dim db As Database

Set db = CurrentDb

dteMon = DateSerial(Year(Date), 1, 1)
If Weekday(dteMon) <> 2 Then
    dteMon = dteMon + 7 - Weekday(dteMon) + 2
End If

'Create table
strSQL = "Create Table Mondays (MonYear Int,MonWeek DateTime)"

db.Execute strSQL

For i = 0 To 51
'Insert records
    strSQL = "Insert Into Mondays (Monyear,MonWeek) Values (" _
    & Year(Date) & ",#" & Format(DateAdd("ww", i, dteMon), "yyyy/mm/dd") & "# )"
    db.Execute strSQL
Next

Debug.Print Format(DMin("MonWeek", "Mondays"), "ww d mmm")
Debug.Print Format(DMax("MonWeek", "Mondays"), "ww d mmm")
End Sub

 

[lbigk]

JB,

Thank you very much, it certainly pointed me in the right direction. I didn't consider DateAdd function before, I should be able to figure it out from here.

Thank you again,

lbigk

 

[lbigk]

Thank you soooo much, between the 2 posts that I received, I was able to get exactly what I was looking for.

Thank you.

 

[MichaelRed]

then, again, the question is WHY in the (relational) world would anyone statically the Mondays, when the above so clearly shows they may be dynamically derived?



MichaelRed