Advanced Search

Build Calendar Table

/ Published in: Visual Basic
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Sub BuildCalendarPeriodTable(BeginDate As Date, EndDate As Date)
  2.  
  3.     Dim rs As DAO.Recordset
  4.     Dim dt As Date
  5.     Dim mo As Byte
  6.     Dim qt As Byte
  7.     Dim yr As Integer
  8.     Dim firstDay As Date
  9.     Dim lastDay As Date
  10.  
  11.     Set rs = CurrentDb.OpenRecordset("Period")
  12.  
  13.     For dt = BeginDate To EndDate
  14.         ' Only update table on first day of month
  15.         If Day(dt) = 1 Then
  16.             ' Get the month and year
  17.             mo = Month(dt)
  18.             yr = Year(dt)
  19.  
  20.             ' Get first and last days of month
  21.             firstDay = dt
  22.             lastDay = LastDayOfMonth(mo, yr)
  23.  
  24.             Select Case mo
  25.                 Case 1 To 3
  26.                     qt = 1
  27.                 Case 4 To 6
  28.                     qt = 2
  29.                 Case 7 To 9
  30.                     qt = 3
  31.                 Case 10 To 12
  32.                     qt = 4
  33.             End Select
  34.  
  35.             ' Add the row
  36.             With rs
  37.                 .AddNew
  38.                 !PeriodId = yr & Format(mo, "00")
  39.                 !CalMthNm = Format(dt, "mmmm")
  40.                 !CalMthNbr = mo
  41.                 !QtrNbr = qt
  42.                 !YearNbr = yr
  43.                 !PeriodBeginDt = firstDay
  44.                 !PeriodEndDt = lastDay
  45.                 .Update
  46.             End With
  47.         End If
  48.     Next dt
  49.  
  50.     Set rs = Nothing
  51.  
  52. End Sub

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.