Automatic Entry of Start Date and End Date Based on Current Date

20 views
Skip to first unread message

Robert Bollinger

unread,
Nov 6, 2015, 2:55:40 PM11/6/15
to
Hello! I am trying to design a new form that automatically enters a start date and an end date based on the current date.

Here's the form criteria:

The day range for the period is from Thursday through Wednesday

So regardless of what the current day is within that range when queried, the start date should be the previous Thursday of the week based on todays date, and the end date should be the next Wednesday based on todays date (or 7 days after the start date).

So if todays date is Friday, November 6, 2015, the automatic entry dates should be a start date of last Thursday, November 5, and an end date of Wednesday, November 11, 2015.

I'd appreciate any help with this!

Thanks in advance!

RBollin

Robert Bollinger

unread,
Nov 7, 2015, 11:38:09 PM11/7/15
to
I could have used a CASE statement, but . . . and it works! Which is what counts.

Private Sub RefreshPeriod_Click()

If Weekday(Date) = 1 Then
Me![PeriodStart] = WeekdayName(Weekday(Date - 3)) & ", " & Str(Date - 3)
Me![PeriodEnd] = WeekdayName(Weekday(Date + 1)) & ", " & Str(Date + 1)
ElseIf Weekday(Date) = 2 Then
Me![PeriodStart] = WeekdayName(Weekday(Date - 4)) & ", " & Str(Date - 4)
Me![PeriodEnd] = WeekdayName(Weekday(Date + 2)) & ", " & Str(Date + 2)
ElseIf Weekday(Date) = 3 Then
Me![PeriodStart] = WeekdayName(Weekday(Date - 5)) & ", " & Str(Date - 5)
Me![PeriodEnd] = WeekdayName(Weekday(Date + 1)) & ", " & Str(Date + 1)
ElseIf Weekday(Date) = 4 Then
Me![PeriodStart] = WeekdayName(Weekday(Date - 6)) & ", " & Str(Date - 6)
Me![PeriodEnd] = WeekdayName(Weekday(Date)) & ", " & Str(Date)
ElseIf Weekday(Date) = 5 Then
Me![PeriodStart] = WeekdayName(Weekday(Date)) & ", " & Str(Date)
Me![PeriodEnd] = WeekdayName(Weekday(Date + 6)) & ", " & Str(Date + 6)
ElseIf Weekday(Date) = 6 Then
Me![PeriodStart] = WeekdayName(Weekday(Date - 1)) & ", " & Str(Date - 1)
Me![PeriodEnd] = WeekdayName(Weekday(Date + 5)) & ", " & Str(Date + 5)
ElseIf Weekday(Date) = 7 Then
Me![PeriodStart] = WeekdayName(Weekday(Date - 2)) & ", " & Str(Date - 2)
Me![PeriodEnd] = WeekdayName(Weekday(Date + 4)) & ", " & Str(Date + 4)
End If

Jean Aimé Faustin Tapsoba

unread,
Nov 8, 2015, 2:45:48 PM11/8/15
to
Try these numbers. It works for today 8/11/2015 and make the arrangement for the next :
If Weekday(Date) = 1 Then
Reply all
Reply to author
Forward
0 new messages