Date and Weekday Question

[dvernon38]

Hi,

I have a form that I want to display 5 different dates on.
A date for.....
Monday
Tuesday
Wednesday
Thursday
Friday

Issue is that I need on Monday that it shows this Monday, this Tuesday...etc.
But on Tuesday it shows this Tuesdays, Wed's date......But the Monday date now should be Next Mondays Date.

When it's Wed.....it should show this Wed, Thurs, Friday's date.....and Monday and Tuesday would be next weeks dates. And so on for the rest of the week.

I have tried a lot of things but I think I have got myself a little lost. Back to basics maybe.

The dates do not need to be stored in the DB.....just displayed on the screen.

Can someone please point me in the right direction.

Thanks,

Dave

 

[Beetle]

This could probably be improved a bit if I put more thought into it, and it doesn't take into account what you want to happen if you are viewing the form on a Saturday or Sunday, but a function like this;

Function MyCalendar(strDay As String) As Date
    
    Select Case Weekday(Date)
        Case 2
            Select Case strDay
                Case "Monday"
                    MyCalendar = Date
                Case "Tuesday"
                    MyCalendar = DateAdd("d", 1, Date)
                Case "Wednesday"
                    MyCalendar = DateAdd("d", 2, Date)
                Case "Thursday"
                    MyCalendar = DateAdd("d", 3, Date)
                Case "Friday"
                    MyCalendar = DateAdd("d", 4, Date)
            End Select
        Case 3
            Select Case strDay
                Case "Monday"
                    MyCalendar = DateAdd("d", 6, Date)
                Case "Tuesday"
                    MyCalendar = Date
                Case "Wednesday"
                    MyCalendar = DateAdd("d", 1, Date)
                Case "Thursday"
                    MyCalendar = DateAdd("d", 2, Date)
                Case "Friday"
                    MyCalendar = DateAdd("d", 3, Date)
            End Select
        Case 4
            Select Case strDay
                Case "Monday"
                    MyCalendar = DateAdd("d", 5, Date)
                Case "Tuesday"
                    MyCalendar = DateAdd("d", 6, Date)
                Case "Wednesday"
                    MyCalendar = Date
                Case "Thursday"
                    MyCalendar = DateAdd("d", 1, Date)
                Case "Friday"
                    MyCalendar = DateAdd("d", 2, Date)
            End Select
        Case 5
            Select Case strDay
                Case "Monday"
                    MyCalendar = DateAdd("d", 4, Date)
                Case "Tuesday"
                    MyCalendar = DateAdd("d", 5, Date)
                Case "Wednesday"
                    MyCalendar = DateAdd("d", 6, Date)
                Case "Thursday"
                    MyCalendar = Date
                Case "Friday"
                    MyCalendar = DateAdd("d", 1, Date)
            End Select
        Case 6
            Select Case strDay
                Case "Monday"
                    MyCalendar = DateAdd("d", 3, Date)
                Case "Tuesday"
                    MyCalendar = DateAdd("d", 4, Date)
                Case "Wednesday"
                    MyCalendar = DateAdd("d", 5, Date)
                Case "Thursday"
                    MyCalendar = DateAdd("d", 6, Date)
                Case "Friday"
                    MyCalendar = Date
            End Select
    End Select
                    
End Function

Then a form with five text boxes, one for each day ("Monday" - "Friday"). In the Control Source of each text box you call the function like;

=MyCalendar("Monday")
=MyCalendar("Tuesday")

using the appropriate weekday name for each text box.

 

[Mr. B]

You did not indicate which version of Access you are using so I have attache a file for Access 2010 and one for Access 2002-2003. Give the appropriate demo file a try. When it opens, it will display the current or next work day and the next four work days. It always excludes Saturday and Sunday.

The code is in the On Load event of the form.

 

[MarkK]

...or a little simpler...

Private Sub Form_Load()
   Dim i As Integer
   Dim j As Integer
   For i = 0 To 4
      Do While Weekday(Date + i + j, vbMonday) > 5
         j = j + 1
      Loop
      Me.Controls("lblDay" & i + 1).Caption = Date + i + j
   Next
End Sub

 

[dvernon38]

Thanks Everyone for the quick replies.

Lagbolt and Mr. B both your codes are close. They work great but they have the current day first.....and the next 4 days after that. So because its Tuesday we have.......
Tuesday July 31
Wednesday Aug 1
Thursday Aug 2
Friday Aug 3
Monday Aug 6

I need it to always be in weekday order.......So because its Tuesday.

Monday Aug 6
Tuesday July 31
Wednesday Aug 1
Thursday Aug 2
Friday Aug 3

Also if the Form gets Open on the Saturday or Sunday It would show the next coming weeks dates.
Monday Aug 6
Tuesday Aug 7
Wednesday Aug 8
Thursday Aug 9
Friday Aug 10

Access 2003

Thanks again.

Dave

 

[MarkK]

See how the controls are numbered? See how currently the control is selected using the loop index, i + 1? OK, so select the control instead using the Weekday() of the date. Then your Monday will always show up in the first control.
Do you get what I'm saying?
Mark

 

[dvernon38]

Sorry....not with you yet. Can you dumb it down for me a bit more.

Dave