This is something I’ve been wanting to do for quite a while, that is, to create a page dedicated to working with Dates and Times.
I was doing a little more advanced Date/Time VBA programming and decided that now was as good a time as ever to start creating such a page. So here it is.
This article will evolve with time, so come back from time to time.
A little over a year ago I wrote an article regarding retrieving a database object’s created and modified date/times:
In it, I detailed that the Last Modified properties was in fact unreliable and hence there was a bug.
So, at the end of the article, I included a link to a Feedback Portal suggestion I created about the issue in the hopes Microsoft would decided to fix this issue for us all.
Just a quick post today to cover, what appears to be, a simple question.
How can I add a Time value to a Date value?
So say you have a date value (US format – mm/dd/yyyy) of 9/19/2018 and you wanted to append a time value of 7:30 AM, how can this be done?
Well, this is where one has to be aware of a function like TimeSerial(), similar to DateSerial() but for times. Thus, the solution is remarkably simple and would look like
#9/19/2018# + TimeSerial(7,30,0)
Another common use is that we need to build queries/code that uses todays date, using the Date() function, but need the criteria to include a specific time. Well, the same approach can be used in such a scenario as well, and the expression/code would look something like
Date() + TimeSerial(7,30,0)
Another example
Below we want to add the time of 7:30 AM to a date 5 days in the future
DateAdd("d",5,Date()) + TimeSerial(7,30,0)
As you can see, the basic principle can be applied to pretty much any existing code.
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 to my earlier post
So, since I highly recommend completely disabling the built-in date picker and replacing it, I thought I should show a couple of the excellent replacements.
Also, another reason to implement a date picker and stop manual entry by your users is to avoid formatting issues depending on what format the user chose to use when inputting the date. 06/11/11, what’s the day, month and year? No guarantee Access will save it as was intended by the users. However, by using a date picker, you have 100% certainty that the date entered is the date saved!
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