Solved: Date must be a Saturday

[DomFino]

Hi everyone,
I have a form that has a date field. When the date field is double clicked a pop up calendar displays and the user can double click on a date and it populated in the date field. The problem in this case, is the date selected must be a week ending date (Saturday).

What I was trying to figure out is how can I check the date selected by the user and if it is not 7, then make it 7 or the Saturday following the date selected.

The date field name is: WeekEnding
Format: General Date
Input Mask: 99/99/0000;0;_

I think I need code in the Validation Rule of the field, but am not sure.
I saw the following code in another post, but I am not sure if it will do what I need or where the code should go:
NEXT_DAY(<date>+7, 'Saturday')

Thanks
Dom

[Norie]

Dom

Do you already have code for displaying the calendar and inserting the
date into the field?

If you do you could add to it to convert the date to the Saturday.

[DomFino]

Hi Norie,
Thanks for the reply. Yes, I do have code to open the calendar and select the date.

Option Compare Database
Option Explicit
Private Sub AXCalendar1_AfterUpdate()
On Error Resume Next
Me.txtDate = Me.AXCalendar1
End Sub
Private Sub AXCalendar1_DblClick()
On Error Resume Next
Dim datRet As Date

datRet = Me.txtDate
DoCmd.Close acForm, Me.Name

Screen.ActiveControl = datRet

End Sub
Private Sub Form_Load()
On Error Resume Next
Me.AXCalendar1 = Date
Me.txtDate = Date
End Sub

Would I need to do something on the blue line of code above? Something that would add days to make Me.textDate = 7?
Dom

[Norie]

Dom

I'm not sure if that's the correct place to do it.

What's the name of the date field?

[DomFino]

WeekEnding is the control source
The name of the filed is txtBegin6

The code for the double click is as follows:
Private Sub txtBegin6_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmCalendar", , , , , , Screen.ActiveControl.Value
End Sub

Thanks
Dom

[geekgirlau]

Private Sub AXCalendar1_DblClick()
Dim datRet As Date


On Error Resume Next
datRet = Me.txtDate

Do Until Weekday(datRet) = 7
datRet = DateAdd("d", 1, datRet)
Loop

DoCmd.Close acForm, Me.Name

Screen.ActiveControl = datRet
End Sub

[DomFino]

Geekgirlau,
Thank you so much for your reply. I tried it in my application and it works perfectly. I guess it is easy for those who know how :friends:

[Norie]

geekgirlau

Why a loop?