planetthoughtful wrote:
Hi All,
I'm building some reports in Acc97 and using a custom calendar form to
allow users to pick dates with which to report.
I'm wondering if there's an easy way in code to be able to
automatically work out the end-of-week date and end-of-month date from
an arbitrary date value?
To explain a little further. Let's say a user wants to view a weekly
report for last week (ie from Monday 27 March 2006 to Friday 31 March
2006). I'd like the user to be able to pick any date in that range (so
to click on 27 Mar, 28 Mar, 29 Mar etc) and for code to work out that
the date on which the Friday of that week fell was 31 Mar 2006.
Similarly for viewing a monthly report. I'd like the user to be able to
pick any day in March and to then automatically work out that the last
day of March was 31 March.
Any practical code examples would be much appreciated!
Much warmth,
planetthoughtful
For end-of-week date:
A simple listing of the 49 combinations of the 'day desired' by 'date
given' simplified to adjusting by the following number of days:
SundayInWeek: 1 - Weekday(GivenDate)
MondayInWeek: 2 - Weekday(GivenDate)
....
SaturdayInWeek: 7 - Weekday(GivenDate)
which simplified to:
Public Function DateInWeek(vbWeekday As Integer, GivenDate As Date) As
Date
DateInWeek = DateAdd("d", vbWeekday - Weekday(GivenDate), GivenDate)
End Function
Sample Calls:
MsgBox ("Monday's date: " & DateInWeek(vbMonday, Date))
SELECT DateInWeek(2, Date()) AS MondaysDate FROM tblZ;
This function is the same as Ken Snell's:
DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
from:
http://groups.google.com/group/micro...260e69f736c9df
You can use DateAdd("d", 6 - Weekday(ChosenDate), ChosenDate) or use
one of the functions shown above to get Friday's Date. Note that
constants such as vbMonday must be converted to their actual values
inside queries.
James A. Fortune
CD********@FortuneJames.com