How to display and store current Week Ending Date (Saturday's)

[ianw]

I need help in VBA to work out and store week ending dates (Saturday’s) for a
new Time Sheet application.
I would like Saturday’s week ending date to appear on each days use of the
Time Sheet form, that would increment each week.
If the time Sheet form is opened on a Saturday, the week ending date needs
to show as being the same day.
It would also be useful to have the latest week ending date stored as a
singular record in a new table for use in reporting other data using the
latest week ending date.

 

[Wayne-I-M]

Hi

If you have 2 boxes on your form
MyDateBox (where you enter a date)
MySaturdayBox (where you want the next saturday shown - or today if it's Sat
today)

You could have some like this on the AfterUpdate event of your MyDateBox

Private Sub MyDateBox_AfterUpdate()
Me.MySaturdayBox = DateAdd("d", vbSaturday - Weekday(Me.MyDateBox,
vbSunday), Me.MyDateBox)
End Sub

Hope this helps

 

[KenSheridan via AccessMonster.com]

A text box on the form or report with a ControlSource property of:

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

or a similar expression in a computed column in a query will show the current
Saturday week ending date whenever the form is opened.

When you refer to storing the "latest week ending date", if by this you mean
the week ending date for the current week then you don't need to store it
anywhere as you can always compute it using the above expression.

To compute the week ending date in relation to a date in a column in a row in
a table substitute the column name for the Date() function in the above
expression. Again there is no need to store it as it can be computed from
the date in the row in the table.

Note that if you use the expression in a query you must use the literal
values for the days of the week, as above, not the equivalent VBA constants.

Ken Sheridan
Stafford, England