Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 4th, 2003, 09:04 AM
Authorized User
 
Join Date: Jun 2003
Location: Memphis, TN, USA.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Finding last day of a month

Does anyone have any code examples that will help me calculate the last day of a given month?

John
__________________
John
 
Old June 4th, 2003, 09:36 AM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Are you talking about the last day being a number or the last day being a day of the week?

Mike
 
Old June 4th, 2003, 09:41 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It goes like this:
1-Get the month whose last day you want to find
2-Use DateSerial to get the first day of the NEXT month
3-Used DateAdd to subtract one complete day from the date you
found in 2. This is the last day of the month.

Here is some VBScript code:

function GetLastDay(aDate)
    dim intMonth
    dim dteFirstDayNextMonth

    dtefirstdaynextmonth = dateserial(year(adate),month(adate) + 1, 1)
    GetLastDay = Day(DateAdd ("d", -1, dteFirstDayNextMonth))
end function

For Example:

GetLastDay ("12/12/2004") returns 31
GetLastDay ("6/4/2003") returns 30
GetLastDay ("2/5/2000") returns 29
GetLastDay ("2/5/2004") returns 28
 
Old June 5th, 2003, 09:43 PM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dim t As Date

t = Trim(DateAdd("d", -1, Str(Month(Date) + 1) + "/01/" + Trim(Str(Year(Date)))))

txtLastdateOfThisMonth.Value = Trim(Str(Month(t))) + "/" + Trim(Str(Day(t))) + "/" + Trim(Str(Year(t)))
 
Old June 5th, 2003, 10:07 PM
Registered User
 
Join Date: Jun 2003
Location: Lakewood, WA, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here's another example

Public Function GetLastDayOfMonth(intMonth, intYear) As Date

    GetLastDayOfMonth = DateSerial(intYear, intMonth + 1, 0)

End Function

Here's a sample for calling the above function:

Dim datLastDay as date

datLastDay=GetLastDayOfMonth(Month(Date()),Year(Da te)))



John Ruff - The Eternal Optimist :-)
 
Old July 3rd, 2003, 12:11 PM
Registered User
 
Join Date: Jul 2003
Location: Dothan, AL, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

And just to combine the answers given:

Public Function GetLastDay(aDate, rtnOption)
' rtnOption = 1 if last day number is wanted
' rtnOption = 2 if last day date is wanted
' Use this function as follows:
' Dim datLastDay as Integer
' datLastDay=GetLastDay(Date(),1)
' or
' Dim datLastDay As Date
' datLastDay=GetLastDay(Date(),2)

    Dim GetLastDayDate As Date

    ' Use DateSerial to get the date of the day before NEXT month
    GetLastDayDate = DateSerial(Year(aDate), Month(aDate) + 1, 1 - 1)

    Select Case rtnMethod
        Case 1 ' Returns the number of the last day
                ' For example:
                ' GetLastDay ("12/12/2004",1) returns 31
                ' GetLastDay ("6/4/2003", 1) returns 30
                ' GetLastDay ("2/5/2000", 1) returns 29
                ' GetLastDay ("2/5/2004", 1) returns 28
            GetLastDay = Day(GetLastDayDate)
        Case 2 ' Returns the date of the last day
                ' For example:
                ' GetLastDay ("12/12/2004",1) returns "12/31/2004"
                ' GetLastDay ("6/4/2003", 1) returns "6/30/2003"
                ' GetLastDay ("2/5/2000", 1) returns "2/29/2000"
                ' GetLastDay ("2/5/2004", 1) returns "2/28/2004"
            GetLastDay = GetLastDayDate
    End Select
End Function
 
Old September 30th, 2008, 01:37 PM
Registered User
 
Join Date: Sep 2008
Location: Kenmore, WA, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just 1 comment on your example February 2004 was 29 days not 28.
GetLastDay ("2/5/2004", 1) returns 28 should return 29.
For confirmation check http://kalender-365.de/leap-years.php
M.

 
Old September 30th, 2008, 03:52 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

The last day of a given month is simply, e.g. for August 2008, DateSerial(2008, 9, 0). You put the month plus 1 in the month spot of the DateSerial function and a zero in the day spot.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old September 30th, 2008, 06:52 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Mephak: The *code* returns 29, correctly.

It was just his COMMENT that had it wrong.

In VBScript code, anything after an apostrophe on a line is a comment.
    a = 7 ' this is a comment
    ' the entire line is a comment

SerranoG: If you look at that old code of "pmpjr", you'll see that he is using exactly the method you described.

Both of you: WHY are we messing with a thread that is over 5 years old????
 
Old October 6th, 2008, 03:27 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Both of you: WHY are we messing with a thread that is over 5 years old????
1) I was messing with a thread with the last date of 9/30/2008. I didn't notice the dates before that one.

2) Let us worry about it. Thanks.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division




Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding a day on a calendar based on recurring val kriskhoury C# 0 July 2nd, 2007 04:03 PM
get year,month,day,hour... kobystud C# 1 July 16th, 2004 04:45 PM
Asp day of month display karib Classic ASP Databases 10 April 5th, 2004 12:56 AM
1st Week-Day of the Month ticktack Classic ASP Basics 2 January 19th, 2004 10:01 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.