Return to Snippet

Revision: 6890
at June 23, 2008 09:45 by corydeppen


Updated Code
Sub BuildCalendarPeriodTable(BeginDate As Date, EndDate As Date)

    Dim rs As DAO.Recordset
    Dim dt As Date
    Dim mo As Byte
    Dim qt As Byte
    Dim yr As Integer
    Dim firstDay As Date
    Dim lastDay As Date
    
    Set rs = CurrentDb.OpenRecordset("Period")
    
    For dt = BeginDate To EndDate
        ' Only update table on first day of month
        If Day(dt) = 1 Then
            ' Get the month and year
            mo = Month(dt)
            yr = Year(dt)
            
            ' Get first and last days of month
            firstDay = dt
            lastDay = LastDayOfMonth(mo, yr)
            
            Select Case mo
                Case 1 To 3
                    qt = 1
                Case 4 To 6
                    qt = 2
                Case 7 To 9
                    qt = 3
                Case 10 To 12
                    qt = 4
            End Select
            
            ' Add the row
            With rs
                .AddNew
                !PeriodId = yr & Format(mo, "00")
                !CalMthNm = Format(dt, "mmmm")
                !CalMthNbr = mo
                !QtrNbr = qt
                !YearNbr = yr
                !PeriodBeginDt = firstDay
                !PeriodEndDt = lastDay
                .Update
            End With
        End If
    Next dt
    
    Set rs = Nothing

End Sub

Revision: 6889
at June 23, 2008 09:44 by corydeppen


Initial Code
Sub BuildCalendarPeriodTable(BeginDate As Date, EndDate As Date)

    Dim rs As DAO.Recordset
    Dim dt As Date
    Dim mo As Byte
    Dim qt As Byte
    Dim yr As Integer
    Dim firstDay As Date
    Dim lastDay As Date
    
    Set rs = CurrentDb.OpenRecordset("Period")    
    
For dt = BeginDate To EndDate
        ' Only update table on first day of month
        If Day(dt) = 1 Then
            ' Get the month and year
            mo = Month(dt)
            yr = Year(dt)
            
            ' Get first and last days of month
            firstDay = dt
            lastDay = LastDayOfMonth(mo, yr)
            
            Select Case mo
                Case 1 To 3
                    qt = 1
                Case 4 To 6
                    qt = 2
                Case 7 To 9
                    qt = 3
                Case 10 To 12
                    qt = 4
            End Select
            
            ' Add the row
            With rs
                .AddNew
                !PeriodId = yr & Format(mo, "00")
                !CalMthNm = Format(dt, "mmmm")
                !CalMthNbr = mo
                !QtrNbr = qt
                !YearNbr = yr
                !PeriodBeginDt = firstDay
                !PeriodEndDt = lastDay
                .Update
            End With
        End If
    Next dt

    Set rs = Nothing

End Sub

Initial URL


Initial Description


Initial Title
Build Calendar Table

Initial Tags


Initial Language
Visual Basic