Rounding dates up to Monday?

[andrewf10]

Hi all,

I have a 'StartDate' field which must always fall on a Monday and is based on a previous date + interval.

If this StartDate falls on a day of the week other than Monday, I need to add the appropriate number of days to bring it to the following Monday.

This can be done using 6 Update queries (one for each day) such as below but is there a neater way of doing all 6 in one go?

Thanks in advance.


UPDATE JobCards
SET JobCards.[StartDate] = [StartDate]+6
WHERE (((Weekday(Weekday([StartDate])))=3));

 

[RV]

>I have a 'StartDate' field which must always fall on a Monday and is based on a previous date + interval<

So StartDate = PreviousDate + interval?

RV

 

[andrewf10]

Yes its the two added together

 

[RV]

You shouldn't store calculated fields.
I assume you're using a form.
Now base this form on a query, add a calculated field to your query to calculate the StartDate.

Now in your form, create an After update event on both your interval and your "previous date" control box.

Put this code in the events (adjust the code the the names of your controls)

If Weekday(Me.PreviousDate + Me.Interval, vbMonday) <> 1 Then
Me.StartDate= Me.PreviousDate + Me.Interval + 7 - Weekday(Me.PreviousDate + Me.Interval, vbMonday) + 1
End If

RV

 

[mresann]

You can tighten up the code into this single line. This will give the date of the next Monday and return the same date if the date is already Monday. No interval variable is necessary, and you can place this line right into a query or form control without using VBA or macros.

Me.Startdate = _
      CDate(7 - Weekday(Me.PreviousDate, vbMonday) + CLng(Me.PreviousDate))

Your query should resemble this:

UPDATE JobCards
SET JobCards.[StartDate] = 
CDate(7 - Weekday([PreviousDate], vbMonday) + 
CLng([PreviousDate]))

NOTE: The CLong function format is not necessary for proper operation, but I usually use it when a calculation is performed on a date variable, as is prescribed for proper programming protocol. Incidentally, you can use any day of the week by substituting the constant "vbMonday" with any other day you choose.

 

[RV]

mresann,

No interval variable is necessary

Yes it is.
The goal of the interval is to derive a StartDate being PreviousDate plus Interval.
StartDate doesn't always fall on the next Monday
You'd like to determine what the intended StartDate would be hrnce the interval.

RV

 

[mresann]

In reading through andrewf10's post, I gathered that all he was looking for was the date of the following Monday (or Start Date) from a given Date (or Previous Date), if it wasn't already Monday. The interval variable was the number of days andrewf10 originally used in his attempt at the calculation, but is not necessary to find the date of the following Monday, as the formula proves.

 

[RV]

I guess you're quite right, if you take into account the quote on the 6 queries (which I totally overlooked )

RV