Date criteria for autofilling date on a form

[pepper42 via AccessMonster.com]

I could use some help on get a date field to autofill on a form that is based
on three scenerios. The status can be added/updated at any time by the end
users for the Monday report. The problem is arising when Monday is a holiday
and the report is not generated until Tuesday. (I think that Monday should
be the start of the week - need help with this too). I have a table that
contains the mm/dd/yy of the holidays and the weekday of the holidays for the
dlookup. This website has helped alot with the basic pieces of this code,
but I can't seem to put it all together right.

Here are the 3 possible scenerios and my code listed below:
Case 1 - Date that the user enters the update is a Monday so the
WeeklyStatus_Date field is populated with the current date.
Case 2 - Date that the user enters the update is a Tuesday or Wednesday and
the Monday of the same week was a holiday, so the WeeklyStatus_Date field
should be populated with the date of the Monday of the current week.
Case 3 - Date that the user enters the update is a Tuesday - Sunday and the
Monday of the same week was not a holiday, so the WeeklyStatus_Date field
should be populated with the Monday of the next week.

Dim strHoliday As String
Dim strResult As String

'Case 1
If [Forms]![subfrm_WeeklyStatus_Major].[Today] = "Monday" Then
WeeklyStatus_Date = Format(Now(), "mm/dd/yy")

'Case 2
ElseIf [Forms]![subfrm_WeeklyStatus_Major].[Today] <> "Monday" Then
strHoliday = "Monday"
strResult = DLookup("[Holidate]", "tbl_Holiday", "[weekday] = '" _
& strHoliday & "'")
If strResult = True Then WeeklyStatus_Date = Format(DateAdd("d",
2, "mm/dd/yy"))
'Case 3
If varResult = False Then WeeklyStatus_Date = Format(DateAdd("d",
8 - Weekday([Now], 2), [Now]), "mm/dd/yy")
End If


Thanks for your help!

 

[Jeff Boyce]

I may be missing something. It sounds like the date to be recorded is
ALWAYS the Monday of the week in which the record is made.

If I recall, there's a setting you can use to tell Access to start the week
on Monday. That would make Mondays Day 1 of each week.

So if you used some convoluted logic, the Monday of the current week (i.e.,
the week in which today is) would be the day-of-week you get by subtracting
the day-of-the-week-for-today from today's date, then adding one (if I did
the math right...<g>). For example, if you set Monday as the first day of
the week, and if today is Thursday (i.e., day 4), then Date() - 4 will be
the Sunday before, and +1 will bump it to Monday. You can use Access'
day/date-related functions to determine day-number-of-week.

Here's hoping there's a lot simpler and more elegant solution out there!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[pepper42 via AccessMonster.com]

Thanks for your response Jeff.
Sometimes updates are done early on Thursday or Friday for the next week
which means that the date should default to the Monday of the next week.
That is what it is making this a bit confusing.
Thank you for your help on the day-of-week.

 

[Jeff Boyce]

Sorry, I missed that "next week" tweak. So you could use an If ... Then
statement (or a Select Case statement) to evaluate the day-of-week of
today's date, and respond accordingly.

To set the date to NEXT Monday, add the number of days remaining in the week
(i.e., if Thursday = Day 4), plus one.

Regards

Jeff Boyce
Microsoft Office/Access MVP