P
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!
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!