By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,583 Members | 1,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,583 IT Pros & Developers. It's quick & easy.

Access default date, specifically the first Sunday of september of the current year

P: 1
Guys, I need something specific and I just can't make it happen. I've got a date field in my Access form and I want the default value to be the first Sunday of september of the current year. Is this even possible?
Dec 9 '19 #1
Share this Question
Share on Google+
1 Reply


cactusdata
P: 79
That is possible:

Expand|Select|Wrap|Line Numbers
  1. FirstSundaySeptember = DateWeekdayInMonth(DateSerial(Year(Date()), 9, 1), 1, vbSunday)
using this generic function:

Expand|Select|Wrap|Line Numbers
  1. ' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
  2. '
  3. ' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
  4. ' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
  5. '
  6. ' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
  7. '
  8. ' 2019-12-08. Gustav Brock, Cactus Data ApS, CPH.
  9. '
  10. Public Function DateWeekdayInMonth( _
  11.     ByVal DateInMonth As Date, _
  12.     Optional ByVal Occurrence As Integer, _
  13.     Optional ByVal Weekday As VbDayOfWeek = vbUseSystemDayOfWeek) _
  14.     As Date
  15.  
  16.     Const DaysPerWeek            As Integer = 7
  17.     Const MaxWeekdayCountInMonth As Integer = 5
  18.  
  19.     Dim Offset          As Integer
  20.     Dim Month           As Integer
  21.     Dim Year            As Integer
  22.     Dim ResultDate      As Date
  23.  
  24.     ' Validate Weekday.
  25.     Select Case Weekday
  26.         Case _
  27.             vbMonday, _
  28.             vbTuesday, _
  29.             vbWednesday, _
  30.             vbThursday, _
  31.             vbFriday, _
  32.             vbSaturday, _
  33.             vbSunday
  34.         Case Else
  35.             ' vbUseSystemDayOfWeek, zero, none or invalid value for VbDayOfWeek.
  36.             Weekday = VBA.Weekday(DateInMonth)
  37.     End Select
  38.  
  39.     ' Validate Occurence.
  40.     If Occurrence < 1 Then
  41.         ' Find first occurrence.
  42.         Occurrence = 1
  43.     ElseIf Occurrence > MaxWeekdayCountInMonth Then
  44.         ' Find last occurrence.
  45.         Occurrence = MaxWeekdayCountInMonth
  46.     End If
  47.  
  48.     ' Start date.
  49.     Month = VBA.Month(DateInMonth)
  50.     Year = VBA.Year(DateInMonth)
  51.     ResultDate = DateSerial(Year, Month, 1)
  52.  
  53.     ' Find offset of Weekday from first day of month.
  54.     Offset = DaysPerWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysPerWeek) Mod DaysPerWeek
  55.     ' Calculate result date.
  56.     ResultDate = DateAdd("d", Offset, ResultDate)
  57.  
  58.     If Occurrence = MaxWeekdayCountInMonth Then
  59.         ' The latest occurrency of Weekday is requested.
  60.         ' Check if there really is a fifth occurrence of Weekday in this month.
  61.         If VBA.Month(ResultDate) <> Month Then
  62.             ' There are only four occurrencies of Weekday in this month.
  63.             ' Return the fourth as the latest.
  64.             ResultDate = DateAdd("d", -DaysPerWeek, ResultDate)
  65.         End If
  66.     End If
  67.  
  68.     DateWeekdayInMonth = ResultDate
  69.  
  70. End Function
Dec 9 '19 #2

Post your reply

Sign in to post your reply or Sign up for a free account.