Tag Archives: VBA Date/Time

VBA – Generate a Random Date Between Two Dates

Reworking an old demo database, I came across some code to generate a random date between two supplied dates and thought it could be of use to other.

'---------------------------------------------------------------------------------------
' Procedure : GetRndDate
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Get a random date between 2 dates
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' dtStartDate : Minimum Date value
' dtEndDate   : Maximum Date value
'
' Usage:
' ~~~~~~
' dtRnd = GetRndDate(#12/01/2002#, #01/05/2015#)
'           Will return a random date between #12/01/2002# and #01/05/2015#
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-02-07              Initial Release
' 2         2017-02-09              Added check that Start is < to End
'                                   Added Comments to code
' 3         2018-09-20              Updated Copyright
'---------------------------------------------------------------------------------------
Function GetRndDate(dtStartDate As Date, dtEndDate As Date) As Date
    On Error GoTo Error_Handler
    Dim dtTmp                 As Date

    'Swap the dates if dtStartDate is after dtEndDate
    If dtStartDate > dtEndDate Then
        dtTmp = dtStartDate
        dtStartDate = dtEndDate
        dtEndDate = dtTmp
    End If
    
    Randomize
    GetRndDate = DateAdd("d", Int((DateDiff("d", dtStartDate, dtEndDate) + 1) * Rnd), dtStartDate)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetRndDate" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

MS Access – VBA – Number of Weekdays Between Two Dates

Have you ever needed to calculate the number of weekdays (Monday through Friday) there were between two dates, that is exactly what the VBA function below does.

'---------------------------------------------------------------------------------------
' Procedure : CalcNoWeekDays
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Count the number of weekdays between two specified dates
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' dtFirstDate   The first of 2 dates to count the number of weekdays between
' dtLastDate    The second of 2 dates to count the number of weekdays between
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' CalcNoWeekDays(#2010-10-6#,#2010-10-23#)      =23
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Jul-09                 Initial Release
'---------------------------------------------------------------------------------------
Function CalcNoWeekDays(dtFirstDate As Date, dtLastDate As Date) As Integer
On Error GoTo Error_Handler
    Dim dtDay   As Date
    
    'Ensure that the dates provided are in the proper order
    If dtFirstDate > dtLastDate Then
        dtDate1 = dtLastDate
        dtDate2 = dtFirstDate
    Else
        dtDate1 = dtFirstDate
        dtDate2 = dtLastDate
    End If
    
    CalcNoWeekDays = 0 'Initialize our weekday counter variable
    
    For dtDay = dtFirstDate To dtLastDate
        iDayOfWeek = Weekday(dtDay)
        If iDayOfWeek <> vbSunday And iDayOfWeek <> vbSaturday Then
            CalcNoWeekDays = CalcNoWeekDays + 1
        End If
    Next dtDay

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CalcNoWeekDays" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

MS Access – Calendar/Date Picker Control

A very common issue with any documents, not only access databases, is how data is entered by the users, most notably date entries. To ensure proper data entry it is very useful to give your users simple graphical tools and eliminate keyboard entry. To this aim, the following are a few excellent date pickers that can very easily be incorporated into any of your database applications.

Allen Browne’s Popup Calendar a very basic calendar easy to setup and use. This is a form based calendar.
Stephen Lebans Calendar a more advanced calendar (more options – can view 12 months at a time!) equally easy to setup and use. This is an API based calendar.
Arvin Meyer’s Calendar another basic form based calendar which is easy to implement in any database.
CyberCow’s Time Pieces another nice set of tools (calendar/date picker, time picker, …).
Peter Hibbs’ Pop-up Calendar another great alternative which allows for date, or, date and time picking.

It is also very important to note that whenever possible you should always avoid the use of ActiveX controls as they can lead to reference and versioning issues. The calendars listed above will not suffer from such issues.

Also, if you have develop an mdb application working in 2007 and have taken advantage of the pop-calendar included in Access 2007. If you wish you database to be backwards compatible then you must implement your own calendar, as earlier version do not have this functionality! That said, MS’ pop-up date picker is very primitive! That is why I still believe you are much better off using an alternate calendar control with more options and giving you full control over its’ functionality, but that is up to you!

Since originally writing this post, I have create a new one with a few screenshots of each that you may prefer to review, so feel free to check out my article:

Great Access Tools - Calendar Controls

Those of you that follow this blog or have seen my forum posts regarding the built-in date picker know my thoughts on the matter. To put it simply, it is a disgrace that this is what Microsoft supplied with Access for selecting dates. If you want more reasons as to why I think this, refer…

Continue reading →