473,231 Members | 2,015 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 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 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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: D. Alvarado | last post by:
Hello, Does anyone have a PHP 4 one-liner (or two-liner) for extracing a file from a directory in which I know the word "footer" is guaranteed to be in the file name, I know the precise directory...
2
by: normd | last post by:
Am I doing something dumb? Ween searching for posts in this system I enter words from my posts - that I know are in there - and the search keeps coming back with nothing found. I entered the...
1
by: javaguy44 | last post by:
Hi, I'm new to Visual Studio.net...and was wondering if there is a shortcut key to finding properties in the IDE. I used Borland C++ Builder a long time ago, and remembered they had a...
4
by: Charles A. Lackman | last post by:
Hello I have created a Dataview and have sorted it on the Date and ShiftNumber columns this works great, but when I want to use the Find Method it gives me the following error: Expecting 2...
3
by: KL | last post by:
Well, I am back. This time our assignment has us filling a vector and then timing how long it takes to find a spot in the vector to insert a new item, and the time required to insert the item...
6
by: SSG | last post by:
Hai All! I need the optimized code for finding the string length. i dont want to use strlen function......... can anyone know reply........ By S.S.G
9
by: Laurent Bugnion | last post by:
Hi, I am wondering what is the best way to find out which ASP.NET sessions are still active. Here is the reason: I have a custom control which can upload files. It saves the files in a folder...
1
by: avik1612 | last post by:
Hi, I have created a program to find text files in a particular directory or folder. and to find a particular word in that files i finding it difficult to put the list in an array and finding...
3
by: DebuggerCLL | last post by:
This is an A* path finding demo I created a few months ago. I don't see any real uses for it, but it's still neat. http://clindsey.blogspot.com/2008/05/javascript-path-finding-prototype.html
2
by: babe20042004 | last post by:
How would I go about finding every nth number in an array set? For example if i had a set containing the numbers 1-100 int each cell, how would I go about finding every 3rd number in the set.
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.