Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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)))
Reply With Quote
  #5 (permalink)  
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 :-)
Reply With Quote
  #6 (permalink)  
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
Reply With Quote
  #7 (permalink)  
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.

Reply With Quote
  #8 (permalink)  
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
Reply With Quote
  #9 (permalink)  
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????
Reply With Quote
  #10 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:59 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.