473,231 Members | 2,015 Online

# Acc97: finding end-of-week and end-of-month dates

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

---
"Lost in thought"
http://www.planetthoughtful.org

Apr 5 '06 #1
2 2314
Hi

Here are two functions you can add to a code module pass them the date from
the calendar control and they will return the EndOfWeek and EndofMonth
respectively

Dim wd As Integer
Select Case wd
Case 1 'Sunday
Case 2 'Monday
Case 3 'Tuesday
Case 4 'Wednesday
Case 5 'Thursday
Case 6 'Friday
Case 7 'Saturday
End Select
End Function

Dim dt As Date
Dim m As Integer
Dim y As Integer

'1st of this month
dt = CDate(m & "/" & 1 & "/" & y)
'1st of Next Month - 1 day = Last Day of This month)
EndOfMonth = DateAdd("m", 1, dt) - 1
End Function
--
-Dick Christoph

"planetthoughtful" <pl**************@gmail.com> wrote in message
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

---
"Lost in thought"
http://www.planetthoughtful.org

Apr 5 '06 #2
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:

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

Apr 5 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.