473,698 Members | 1,901 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 2345
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
1598
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
1650
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
1245
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
2179
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
2231
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
3703
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
2299
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
1360
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
1829
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
4907
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
8600
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
9156
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...
1
8892
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7712
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...
0
5860
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
4361
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
4614
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3038
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1998
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.