Set date to Monday

  • Thread starter Thread starter DavPet
  • Start date Start date
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?
 
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
 
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]))
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Forecasting Dates Out to the Day 3
Expressions in a table 1
Validate a "Monday" Date 7
Limit date input into query parameters. 2
Schedulig question 1
45 day payment 2
Counting the number of working Mondays 1
simple expression or SQL script to add business days to a date 3

Back
Top