hi,
I had code written for me to create week no and start date increament every 7 days for each week but i would now like to create daily dates with week numbers and the day.
I have managed to create the daily dates but I cant seem to create the week no in intervals of 7 days of the week and the Day.
ie the results I need are
Wk No Date Day Year
1 01/01/10 fri 2010
1 02/01/10 sat 2010
1 03/01/10 sun 2010 ...etc
then for wk no 2
2 08/01/10 fri 2010
2 09/01/10 sat 2010
2 010/01/10 sun 2010 ...etc
my Current Result
WeekNo Date Day YearDate
1 01/01/2009 0 2010
2 02/01/2009 0 2010
3 03/01/2009 0 2010
4 04/01/2009 0 2010
5 05/01/2009 0 2010
6 06/01/2009 0 2010
7 07/01/2009 0 2010
8 08/01/2009 0 2010
9 09/01/2009 0 2010
10 10/01/2009 0 2010
11 11/01/2009 0 2010
My Code
Sub DailyDate()
'This section sets up all the variables used in the procedure
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form
Dim intCount As Integer
Dim dteStart As Date
Dim dteEnd As Date
Dim dteStartDate As Date
Dim dteEndDate As Date
Dim dteYearDate
Dim txtBranchID As Recordset
Dim liRecordIndex As Integer
On Error GoTo Error_Handler
Set dbs = CurrentDb
Set rst = dbs.OpenRecords et("DailyDate" )
Set frm = Forms("frmCreat eDay")
'This section gets the values from the textboxes on the form
'and sets intCount to 1 (intCount tracks the week number)
dteStartDate = frm.Controls("t xtStart")
dteEndDate = frm.Controls("t xtEnd")
dteYearDate = frm.Controls("t xtYear")
intCount = 1
'This section sets the start and end dates for the first week
dteStart = dteStartDate
dteEnd = dteStart + 1
'This loops through until the end date is reached
'If your end date doesn't fall properly at the end of a week it will
'cut off at the last week end date BEFORE the total date is reached
'Each time it loops through it adds one record to the table
Do Until dteEnd > dteEndDate
With rst
.AddNew
!WeekNo = intCount
!Date = dteStart
!YearDate = (dteYearDate)
.Update
End With
'Increments the start and end dates and the week number
intCount = intCount + 1
dteStart = dteStart + 1
dteEnd = dteStart
Loop
Set frm = Nothing
Set rst = Nothing
Set dbs = Nothing
intCount = MsgBox("Weeks created.", vbOKOnly)
Exit_Sub:
Exit Sub
Error_Handler:
If Err.Number = 3022 Then
MsgBox "Weeks already exist!", vbOKOnly + vbCritical, "Error!"
Else
MsgBox Err.Number & vbCrLf & Err.Description , vbOKOnly + vbCritical, "Error!"
End If
Exit Sub
End Sub
Can you please help as I am not familiar with vb to much
Thanks in advance
Farid
I had code written for me to create week no and start date increament every 7 days for each week but i would now like to create daily dates with week numbers and the day.
I have managed to create the daily dates but I cant seem to create the week no in intervals of 7 days of the week and the Day.
ie the results I need are
Wk No Date Day Year
1 01/01/10 fri 2010
1 02/01/10 sat 2010
1 03/01/10 sun 2010 ...etc
then for wk no 2
2 08/01/10 fri 2010
2 09/01/10 sat 2010
2 010/01/10 sun 2010 ...etc
my Current Result
WeekNo Date Day YearDate
1 01/01/2009 0 2010
2 02/01/2009 0 2010
3 03/01/2009 0 2010
4 04/01/2009 0 2010
5 05/01/2009 0 2010
6 06/01/2009 0 2010
7 07/01/2009 0 2010
8 08/01/2009 0 2010
9 09/01/2009 0 2010
10 10/01/2009 0 2010
11 11/01/2009 0 2010
My Code
Sub DailyDate()
'This section sets up all the variables used in the procedure
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form
Dim intCount As Integer
Dim dteStart As Date
Dim dteEnd As Date
Dim dteStartDate As Date
Dim dteEndDate As Date
Dim dteYearDate
Dim txtBranchID As Recordset
Dim liRecordIndex As Integer
On Error GoTo Error_Handler
Set dbs = CurrentDb
Set rst = dbs.OpenRecords et("DailyDate" )
Set frm = Forms("frmCreat eDay")
'This section gets the values from the textboxes on the form
'and sets intCount to 1 (intCount tracks the week number)
dteStartDate = frm.Controls("t xtStart")
dteEndDate = frm.Controls("t xtEnd")
dteYearDate = frm.Controls("t xtYear")
intCount = 1
'This section sets the start and end dates for the first week
dteStart = dteStartDate
dteEnd = dteStart + 1
'This loops through until the end date is reached
'If your end date doesn't fall properly at the end of a week it will
'cut off at the last week end date BEFORE the total date is reached
'Each time it loops through it adds one record to the table
Do Until dteEnd > dteEndDate
With rst
.AddNew
!WeekNo = intCount
!Date = dteStart
!YearDate = (dteYearDate)
.Update
End With
'Increments the start and end dates and the week number
intCount = intCount + 1
dteStart = dteStart + 1
dteEnd = dteStart
Loop
Set frm = Nothing
Set rst = Nothing
Set dbs = Nothing
intCount = MsgBox("Weeks created.", vbOKOnly)
Exit_Sub:
Exit Sub
Error_Handler:
If Err.Number = 3022 Then
MsgBox "Weeks already exist!", vbOKOnly + vbCritical, "Error!"
Else
MsgBox Err.Number & vbCrLf & Err.Description , vbOKOnly + vbCritical, "Error!"
End If
Exit Sub
End Sub
Can you please help as I am not familiar with vb to much
Thanks in advance
Farid