473,848 Members | 1,628 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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,

planetthoughtfu l

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

Apr 5 '06 #1
2 2357
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(ADat e 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

"planetthoughtf ul" <pl************ **@gmail.com> wrote in message
news:11******** *************@g 10g2000cwb.goog legroups.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,

planetthoughtfu l

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

Apr 5 '06 #2
planetthoughtfu l 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,

planetthoughtfu l


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(GivenDa te)
MondayInWeek: 2 - Weekday(GivenDa te)
....
SaturdayInWeek: 7 - Weekday(GivenDa te)

which simplified to:

Public Function DateInWeek(vbWe ekday As Integer, GivenDate As Date) As
Date
DateInWeek = DateAdd("d", vbWeekday - Weekday(GivenDa te), GivenDate)
End Function

Sample Calls:
MsgBox ("Monday's date: " & DateInWeek(vbMo nday, Date))
SELECT DateInWeek(2, Date()) AS MondaysDate FROM tblZ;

This function is the same as Ken Snell's:

DateOfSpecificW eekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)

from:

http://groups.google.com/group/micro...260e69f736c9df
You can use DateAdd("d", 6 - Weekday(ChosenD ate), 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********@Fort uneJames.com

Apr 5 '06 #3

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

Similar topics

1
1614
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 to search, and I am assured that at most one file will match this description. I am looking to get the complete path to this file. Thanks in advance, - Dave
2
1664
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 original; I KNOW it is there Further, I search, as a test, for posts I'm looking at on the screen and using words from their subject line, and it tells me "nothing found! Is there a way to find posts from an author without starting with a post from...
1
1257
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 property finder... Anyone know where it is in Visual Studio? Or how to set one up in 'Options'?
4
2187
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 value(s) for the key being indexed, but received 1 value(s). I have tried the following: Dim Names(1) as Object
3
2239
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 after that spot is found. Well, I am kinda lost on this finding things in a vector. I mean, wouldn't you just go to vector, and then ....well then I gotta find out how to do an insert in a vector. Does this involve changing the pointer to point...
6
3710
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
2314
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 named after the SessionID. At Session_End, I do some cleanup and delete the "session folder". However, if the PC is rebooted before the session times out, the clean up never happens. So what I wanted to do isL: In my CleanUp method, check all...
1
1365
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 the words I have pasted the code below
3
1836
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
5008
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
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10997
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10661
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7067
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5731
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5917
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4134
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.