Access date functionality

[shankindc]

Hi,
I have the following requirements

In a form, there exists a date field which needs to be automatically populated. The date has to be the Friday of every week. Assume in a calendar week Feb 4th - Feb 11th, if the user opens a form for data entry anytime between Feb 4th(Sunday) - Feb 9th (Friday), the data field should populate with Feb 9th date. If the user opens the form anytime after Feb 9th, the date field should populate with the next week's friday's date i.e. Feb 16th.

Can anyone pls help me with this?

Thanks,
Shank

[maxamis4]

Take a look at this link it should help you with what you want.

For example this here will return the last sunday

Week Beginning: DateAdd("d",1-Weekday([GYourdateField]),[YourDateField])

Weekday is an Access built in function.

Good luck

[maxamis4]

Here is some code I made for you. There are really better ways to make this with the date functions but this works too. You can do this on the On Form Load or On Current

Good luck

Private Sub btnSubmit_Click()
    Dim myDate As Date
    myDate = Date ' Date is equal to today's date
    
    ' The weeks starts with sunday which equals 1 Friday = 6
    If Weekday(myDate) >= 2 And Weekday(myDate) <= 6 Then
        Do While Weekday(myDate) <> 6 ' If our date is not Friday's date
        
        myDate = DateAdd("d", 1, myDate) 'Adds a day to my date to find out the date for friday

        Loop
        Me.txtWeekofDate = myDate 'Sets your control equal to that date
    Else
        myDate = DateAdd("d", 2, myDate) ' Adds two days to account for the fact that it might be Saturday.
        If Weekday(myDate) >= 2 And Weekday(myDate) <= 6 Then
            Do While Weekday(myDate) <> 6
        
             myDate = DateAdd("d", 1, myDate) 'Adds a day to my date to find out the date for friday

            Loop
        End If
        Me.txtWeekofDate = myDate
        
        
    End If

    
End Sub

[NeoPa]

Try setting Default property of the control to :

=DateAdd("d",7-Weekday(Date(),7),Date())

[shankindc]

Here is some code I made for you. There are really better ways to make this with the date functions but this works too. You can do this on the On Form Load or On Current

Good luck

Private Sub btnSubmit_Click()
    Dim myDate As Date
    myDate = Date ' Date is equal to today's date
    
    ' The weeks starts with sunday which equals 1 Friday = 6
    If Weekday(myDate) >= 2 And Weekday(myDate) <= 6 Then
        Do While Weekday(myDate) <> 6 ' If our date is not Friday's date
        
        myDate = DateAdd("d", 1, myDate) 'Adds a day to my date to find out the date for friday

        Loop
        Me.txtWeekofDate = myDate 'Sets your control equal to that date
    Else
        myDate = DateAdd("d", 2, myDate) ' Adds two days to account for the fact that it might be Saturday.
        If Weekday(myDate) >= 2 And Weekday(myDate) <= 6 Then
            Do While Weekday(myDate) <> 6
        
             myDate = DateAdd("d", 1, myDate) 'Adds a day to my date to find out the date for friday

            Loop
        End If
        Me.txtWeekofDate = myDate
        
        
    End If

    
End Sub

Thanks so much for the quick response. A question on your logic.

//myDate = DateAdd("d", 2, myDate) ' Adds two days to account for the fact that it might be Saturday.

When I did some research, i found that Saturday has a weekday value of 7. I presume your logic works on the assumption that Saturday has a value 0? or maybe my understanding is incorrect? And is there a specific reason you chose the range 2 to 6 for weekday instead of 1 to 6?

Thanks again!

[NeoPa]

If you look at post #4 you'll see that this really doesn't need to be this complicated.

[shankindc]

If you look at post #4 you'll see that this really doesn't need to be this complicated.

Hi NeoPa,
Thanks for your reply. It works fine. Just curious about how the logic works in your case. I understand that Weekday(Date()) returns a number from 1-7 and date add adds a particular # of days to the current date.

In your suggestion, it says
DateAdd("d",7-Weekday(Date(), 7),Date())

Weekday(Date(), 7) - does this take the smaller of the two parameters Weekday(Date() and the value 7? And also, how does it take care that it always updates to the following Friday's date?

Thanks again!!!

[NeoPa]

No Problem.

=DateAdd("d",
         7-
           Weekday(Date(),7),
         Date())

• Weekday(Date(), 7) returns the weekday number but starting from 1=Saturday rather than 1=Sunday.
• 7-Weekday(Date(), 7) is therefore, the number of days until Friday. Saturday=1 so 7-1=6. Friday=7 so 7-7=0.

[shankindc]

No Problem.

=DateAdd("d",
         7-
           Weekday(Date(),7),
         Date())

• Weekday(Date(), 7) returns the weekday number but starting from 1=Saturday rather than 1=Sunday.
• 7-Weekday(Date(), 7) is therefore, the number of days until Friday. Saturday=1 so 7-1=6. Friday=7 so 7-7=0.

Wow! didnt know we could manipulate Weekday function this way.. Thanks for the explanation.

[NeoPa]

No problem.

1. Go to your VBA window (Alt-F11 from the Access window).
2. Go to your Immediate window (Ctrl-G).
3. Type in a function (EG. Weekday).
4. Press F1 for context-sensitive Help.

This can give you information about all sorts of things within Access.

[shankindc]

No problem.

1. Go to your VBA window (Alt-F11 from the Access window).
2. Go to your Immediate window (Ctrl-G).
3. Type in a function (EG. Weekday).
4. Press F1 for context-sensitive Help.

This can give you information about all sorts of things within Access.

Thanks. Did try that out. Can you pls help me with the thread "Insert into" creating issues?

[NeoPa]

That's a new one today. I'll have to get to it as and when I can I'm afraid (I have a number of threads I'm trying to keep up with that I'm active in atm). I should get around to that later this evening or tomorrow.