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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

planetthoughtful - 05 Apr 2006 03:19 GMT

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

Reply to this Message

DickChristoph - 05 Apr 2006 04:46 GMT

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

Function EndOfWeek(ADate As Date)
Dim wd As Integer
wd = Weekday(ADate)
Select Case wd
Case 1 'Sunday
EndOfWeek = ADate + 5
Case 2 'Monday
EndOfWeek = ADate + 4
Case 3 'Tuesday
EndOfWeek = ADate + 3
Case 4 'Wednesday
EndOfWeek = ADate + 2
Case 5 'Thursday
EndOfWeek = ADate + 1
Case 6 'Friday
EndOfWeek = ADate + 1
Case 7 'Saturday
EndOfWeek = ADate + 6
End Select
End Function

Function EndOfMonth(ADate As Date)
Dim dt As Date
Dim m As Integer
Dim y As Integer

m = Month(ADate)
y = Year(ADate)
'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

Signature

-Dick Christoph

> Hi All,
>
[quoted text clipped - 24 lines]
> "Lost in thought"
> http://www.planetthoughtful.org

Reply to this Message

CDMAPoster@FortuneJames.com - 05 Apr 2006 08:22 GMT

> Hi All,
>
[quoted text clipped - 20 lines]
>
> 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/microsoft.public.access/msg/13260e69f736c9df

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
CDMAPoster@FortuneJames.com

Reply to this Message