Broadcast Dates Query/Table 2

[newguy86]

So here's what I'm looking to accomplish, I basically need a table full of dates along with other bits of information but more so than that I need a way for the table to update itself automatically without a user having to manually input dates and that's where I thought VBA would be needed.

Here's the table structure that I've already laid out:

Cal_Date (Calendar Date, Date/Time)
DotW (Day of the Week, Text)
Cal_Mo (Calendar Month, Text)
Cal_Yr (Calendar Year, Number)
BCST_Mo (Broadcast Month, Text)
BCST_Yr (Broadcast Year, Number)
Week_Num (Week Number, Number)

I would like the table to be filled with 4 years of dates (2 years in the past, 2 years in the future) and I figure that the best way to accomplish that would be to use some sort of date function in VBA/SQL like "Date(Now())" and then just use something like "+/- 730" to get the date range that I need. And I would assume that there's something in VBA/SQL that I could use to get the Day of the Week, Calendar Month, Calendar Year, and Week Number to be included with the date in a query I just haven't found all of the pieces yet that I'm looking for. So if that part is simple and you want to throw that in with the main issue that I'm having then that would be awesome.

But the main problem that I have is trying to determine the Broadcast Month and Broadcast Year for a specific date. Here's an example of what I'm talking about:

Cal_Date.......DotW....Cal_Mo.......Cal_Yr...BCST_Mo...BCST_Yr..Week_Num
12/29/2014...Mon......December...2014.....January.....2015........1

As you can see with the 12/29 date provided the calendar month and year is December 2014 but the broadcast month and year is January 2015 because it falls in the first week of the new year.

Now what I'm trying to do is figure out how to put all of this information into a table that we can reference for reporting and whatnot.

Any suggestions?

A few things to explain in order to help clear things up and answer any questions:
1. Broadcast Weeks always go from Monday-Sunday
2. The 1st of the month will always have the same broadcast month/year as the calendar month/year (the same applies with the first half or so of the month)
3. The main issue is with the last few days of the month when they fall in the same week as the 1st of the following month.

Travis
Charter Media

 

[dhookom]

I would just create the table in Excel for about 20 years and then paste it into an Access table. Writing the code and figuring the logic would take a longer time with no gain.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

If you have Cal_Date as Date (it is actually just a number), you can easily get out of it: DotW, Cal_Mo, and Cal_Yr. No need to / do not keep this information in a table. And if you can establish very specific business rules of how BCST_Mo, BCST_Yr, and Week_Num should be calculated, you don’t need to keep it in a table, either.

So it looks to me that you just need a Sub/Function that will accept a Date, and calculate all other ‘pieces’ that you need. No need for a table at all.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[newguy86]

dhookom,
I may end up having to do that. I was trying to avoid it if at all possible since I figured that it would have loads of applications if I could figure out how to do it automatically.

Andrzejek,
Then I guess that's what I'm trying to figure out. Any suggestions on how to calculate broadcast month and year based on what I described earlier?

Travis
Charter Media

 

[Andrzejek]

To deal with the first 4 fields in your proposed table, consider this:

Dim lngDate As Long

lngDate = CLng(Date)
Debug.Print "Today date is       " & Date & " which is " & lngDate
Debug.Print "Year is             " & Year(lngDate)
Debug.Print "Month number is     " & Month(lngDate)
Debug.Print "Full Month name is  " & MonthName(Month(lngDate))
Debug.Print "Short Month name is " & Format(lngDate, "MMM")
Debug.Print "Full day name is    " & WeekdayName(Weekday(lngDate))
Debug.Print "Short day name is   " & Format(lngDate, "DDD")

As far as ‘broadcast’ fields, first you need to ‘get your ducks in a row’.
In your OP you state:[tt]
BCST_Mo (Broadcast Month, Text)
BCST_Yr (Broadcast Year, Number)[/tt]
But later in the same post you mention:
1. Broadcast Weeks always go from Monday-Sunday
So do you need to know ‘Broadcast Weeks’? There is nothing about it in your table.
2. The 1st of the month will always have the same broadcast month/year as the calendar month/year (the same applies with the first half or so of the month)
‘first half or so of the month’ is not what computers would understand.

Like I stated before: you need to "establish very specific business rules of how BCST_Mo, BCST_Yr, and Week_Num should be calculated" and implement it in your code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

You need to make a loop and build a sql insert string. Then run an insert query. It needs to be in the format like this. Other fields can be added once you get your rules.
Insert into tblBroadcastDates (Cal_Date, DotW, Cal_Mo, Cal_Yr) values (#06/17/2020#, 'Wednesday', 'Jun', 2020)
Building a string with all the # and ‘ gets confusing. So I use some common functions to help wrap the values.

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function
Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

So your loop then looks like

Public Sub FillDates()
  Const tableName = "tblBroadcastDates"
  Const StartDate = #1/1/2015#
  Const EndDate = #12/31/2020#
  
  Dim IterativeDate As Date
  Dim strDate As String
  Dim strSql As String
  Dim DotW As String
  Dim cal_Mo As String
  Dim Cal_Yr As Integer

  IterativeDate = StartDate
  Do While IterativeDate < EndDate
    strDate = SQLDate(IterativeDate)
    DotW = WeekdayName(Weekday(IterativeDate))
    DotW = sqlTxt(DotW)
    cal_Mo = Format(IterativeDate, "MMM")
    cal_Mo = sqlTxt(cal_Mo)
    Cal_Yr = Year(IterativeDate)
    strSql = "Insert into " & tableName & " (Cal_Date, DotW, Cal_Mo, Cal_Yr) values (" & strDate & ", " & DotW & ", " & cal_Mo & ", " & Cal_Yr & ")"
    IterativeDate = IterativeDate + 1
    Debug.Print strSql
    CurrentDb.Execute strSql
  Loop
  For i = 0 To numberOfDays
  Next i
End Sub

You do not really need to save, the Month, Year, DotW because you can use these same functions in a query. You really just need to save the date. However, this is a reference table, and the values are never going to change. We are never going to drop a day or add a day to the current calendar. In this case it would improve performance.

 

[newguy86]

Andrzejek,
My apologies for not explaining this better. What I meant by broadcast weeks is that the weeks in a broadcast month start on Monday and end on Sunday. And unlike normal calendar months where the 1st and the 30th/31st can fall on any day of the week, broadcast months always start on a Monday and end on a Sunday and can either be 4 or 5 weeks long depending on how the month is laid out.

For example:

March 2015 - The broadcast month starts on Monday 2/23 (because the 1st of March is that Sunday) and ends on Sunday 3/29. Which results in having 5 weeks in the month.
April 2015 - The broadcast month starts on Monday 3/30 (because the 1st of April is that Wednesday) and ends on Sunday 4/26. Which results in having 4 weeks in the month.

Now as far as defining specific business rules around this, the only way that I can think of logically to approach this is to somehow look at each week as a whole and have a rule that states "If within any day of the week Cal_Date = 1st of the month, then BCST_Mo and BCST_Yr for every day of that week is equal to the Cal_Mo and Cal_Yr of the 1st of the month".

But I can't think of how to implement that into code.

MajP,
Thank you for your suggestion. I was headed down a similar path and will probably just setup a function that we can use whenever we're processing reports/data and need that info.

Travis
Charter Media

 

[MajP]

For you Broad cast. Here is all you need

Public Function GetFridayOfWeek(dtmDate As Date) As Date
   GetFridayOfWeek = DateAdd("d", vbFriday - Weekday(dtmDate, vbSunday), dtmDate)
End Function

For every date you need to find the Friday for that week.
The Friday of the week determines the broadcast month and year and the week number.

Same thing for the week of the year. Find Friday of the week. If that Friday is in the first week of the new year it is the first week of the year.

Complete solution

Public Sub FillDates()
  Const tableName = "tblBroadcastDates"
  Const StartDate = #1/1/2015#
  Const EndDate = #12/31/2017#
  
  Dim IterativeDate As Date
  Dim strDate As String
  Dim strSql As String
  Dim DotW As String
  Dim cal_Mo As String
  Dim Cal_Yr As Integer
  Dim BCST_Mo As String
  Dim BCST_Yr As Integer
  Dim Week_Num As Integer
  Dim FridayOfWeek As Date
  IterativeDate = StartDate
  Do While IterativeDate < EndDate
    strDate = SQLDate(IterativeDate)
    DotW = WeekdayName(Weekday(IterativeDate))
    DotW = sqlTxt(DotW)
    cal_Mo = Format(IterativeDate, "MMM")
    cal_Mo = sqlTxt(cal_Mo)
    Cal_Yr = Year(IterativeDate)
    FridayOfWeek = GetFridayOfWeek(IterativeDate)
    BCST_Mo = Format(FridayOfWeek, "MMM")
    BCST_Mo = sqlTxt(BCST_Mo)
    BCST_Yr = Year(FridayOfWeek)
    Week_Num = DatePart("ww", FridayOfWeek, vbSunday)
    strSql = "Insert into " & tableName & " (Cal_Date, DotW, Cal_Mo, Cal_Yr,BCST_Mo, BCST_Yr, Week_Num) values "
    strSql = strSql & "(" & strDate & ", " & DotW & ", " & cal_Mo & ", " & Cal_Yr & "," & BCST_Mo & "," & BCST_Yr & ", " & Week_Num & ")"
    IterativeDate = IterativeDate + 1
    Debug.Print strSql
    CurrentDb.Execute strSql
  Loop
  For i = 0 To numberOfDays
  Next i
End Sub
Public Function GetFridayOfWeek(dtmDate As Date) As Date
   GetFridayOfWeek = DateAdd("d", vbFriday - Weekday(dtmDate, vbSunday), dtmDate)
End Function

 

[MajP]

oh you changed the rules
Your week ends on a Sunday. So for a date from Monday to Sunday then ending sunday determines which week and month.

Public Sub FillDates()
  Const tableName = "tblBroadcastDates"
  Const StartDate = #1/1/2015#
  Const EndDate = #12/31/2017#
  
  Dim IterativeDate As Date
  Dim strDate As String
  Dim strSql As String
  Dim DotW As String
  Dim cal_Mo As String
  Dim Cal_Yr As Integer
  Dim BCST_Mo As String
  Dim BCST_Yr As Integer
  Dim Week_Num As Integer
  Dim EndingSunday As Date
  IterativeDate = StartDate
  Do While IterativeDate < EndDate
    strDate = SQLDate(IterativeDate)
    DotW = WeekdayName(Weekday(IterativeDate))
    DotW = sqlTxt(DotW)
    cal_Mo = Format(IterativeDate, "MMM")
    cal_Mo = sqlTxt(cal_Mo)
    Cal_Yr = Year(IterativeDate)
    EndingSunday = GetEndingSunday(IterativeDate)
    BCST_Mo = Format(EndingSunday, "MMM")
    BCST_Mo = sqlTxt(BCST_Mo)
    BCST_Yr = Year(EndingSunday)
    Week_Num = DatePart("ww", IterativeDate, vbMonday)
    'Handle week 53
    If Week_Num = 53 Then
      If Weekday(IterativeDate) = vbSunday Then
        Week_Num = 52
      Else
        Week_Num = 1
      End If
     End If
     strSql = "Insert into " & tableName & " (Cal_Date, DotW, Cal_Mo, Cal_Yr,BCST_Mo, BCST_Yr, Week_Num) values "
    strSql = strSql & "(" & strDate & ", " & DotW & ", " & cal_Mo & ", " & Cal_Yr & "," & BCST_Mo & "," & BCST_Yr & ", " & Week_Num & ")"
    IterativeDate = IterativeDate + 1
    Debug.Print strSql
    CurrentDb.Execute strSql
  Loop
  For i = 0 To numberOfDays
  Next i
End Sub

Public Function GetEndingSunday(dtmDate As Date) As Date
   If Weekday(dtmDate) = vbSunday Then
     GetEndingSunday = dtmDate
   Else
      GetEndingSunday = DateAdd("d", vbSunday - Weekday(dtmDate, vbSunday) + 7, dtmDate)
   End If
End Function

The week num get a little wacky, but I believe it is correct.

 

[Andrzejek]

No apologies needed.
My point was – business rules have to be established and translated into a working piece of code / logic. Something that MajP has done here. If that logic ALWAYS is going to work for you, great. If not, it needs to be modified.
And that’s true for both: establishing a table with the data, or creating a function to calculate all of that on-the-fly.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[newguy86]

Perfect! Works like a charm!

Travis
Charter Media