Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Date/Time: Various Date manipulation functions

Author(s)
Lewis Moseley

    Here are some simple date manipulation functions that can be used to return a specific date in the future/past.

    Note that you can combine these functions to, for example, find the lastday of next month:

        newdate = LastOfMonth( NextMonth( olddate ) )

'*************************** Code Start *******************************
'This code was originally written by Lewis Moseley.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Lewis Moseley
'
Function FirstOfMonth(InputDate As Date)
'  Return a date that is the first day of the month of the date passed
Dim D As Integer, M As Integer, Y As Integer

    If IsNull(InputDate) Then
        FirstOfMonth = Null
    Else
        D = Day(InputDate)
        M = Month(InputDate)
        Y = Year(InputDate)
        FirstOfMonth = DateSerial(Y, M, 1)
    End If
End Function

Function LastOfMonth(InputDate As Date)
'  Return a date that is the last day of the month of the date passed
Dim D As Integer, M As Integer, Y As Integer
    
    If IsNull(InputDate) Then
        LastOfMonth = Null
    Else
        D = Day(InputDate)
        M = Month(InputDate)
        Y = Year(InputDate)
        'find the first day of next month, then back up one day
        LastOfMonth = DateAdd("m", 1, DateSerial(Y, M, 1)) - 1
    End If
End Function

Function NextMonth(InputDate As Date)
'  Return a date that is one month later than the date passed
    NextMonth = DateAdd("m", 1, InputDate)
End Function

Function LastMonth(InputDate As Date)
'  Return a date that is one month before the date passed
   LastMonth = DateAdd("m", -1, InputDate)
End Function

Function SetDayOfMonth(InputDate As Date, DayToSet As Integer)
'  Return a date that is the specified day of the month and year passed
Dim M As Integer, Y As Integer

    If IsNull(InputDate) Then
        SetDayOfMonth = Null
    Else
        M = Month(InputDate)
        Y = Year(InputDate)
        SetDayOfMonth = DateSerial(Y, M, DayToSet)
    End If
End Function
'*************************** Code End  *******************************

1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer