471,579 Members | 1,470 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,579 software developers and data experts.

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 2169
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
--
-Dick Christoph

"planetthoughtful" <pl**************@gmail.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...
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:

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

Apr 5 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by D. Alvarado | last post: by
2 posts views Thread by normd | last post: by
4 posts views Thread by Charles A. Lackman | last post: by
3 posts views Thread by KL | last post: by
6 posts views Thread by SSG | last post: by
9 posts views Thread by Laurent Bugnion | last post: by
1 post views Thread by avik1612 | last post: by
3 posts views Thread by DebuggerCLL | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
1 post views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.