Set date to Monday

D

DavPet

My form allows a date to be entered.
How can I let the user enter a date, then programmatically change that date
to the previous Monday?
 
D

dan artuso

Hi,
Here is some code that you can place on the After Update event of your
control.
Substitute the real name of the control for yourControl.

I've only done minimal testing, so make sure you test this for all possible
weekdays!

Dim intDay As Integer
Dim myDate As Date

myDate = yourControl
intDay = Weekday(myDate)

MsgBox intDay

Select Case intDay
Case 3
yourControl = DateAdd("d", -(intDay - 2), myDate)
Case 4
yourControl = DateAdd("d", -(intDay - 2), myDate)
Case 5
yourControl = DateAdd("d", -(intDay - 2), myDate)
Case 6
yourControl = DateAdd("d", -(intDay - 2), myDate)
Case 7
yourControl = DateAdd("d", -(intDay - 2), myDate)
Case 1
yourControl = DateAdd("d", -(intDay - 1), myDate)
End Select
 
K

KARL DEWEY

In your data entry form have it call a macro after update of the textbox.
Use the condition of the query to check if the date enter is other than
Monday. Use SetValue to change the textbox.

Below will convert to the previous Monday. Replace [Date Open] with your
object name ( [Forms]![YourFormName]![YourTextBox] ).

IIf(Weekday([Date open])=2,[Date Open],DateAdd("d",-Weekday([Date
open])+2,[Date Open]))
 
K

Ken Sheridan

In the controls after update event procedure put:

[MyDate] = [MyDate] - Weekday([MyDate],vbMonday) + 1

where MyDate is the control in question.

Ken Sheridan
Stafford, England
 
Top