MS Access™ MVP Awardee: 2007-2017
Blog: GPG on Access
Published Books:
Professional Access 2013 Programming with co-authors Teresa Hennig, Ben Clothier and Doug Yudovich. Grover Park George On Access MS Access™ in a SharePoint World with co-authors Tim Runcie and Ben Clothier.
Some of the most useful functions you'll ever use in Visual Basic for Access are the Date() function and some of the related functions, such as Datepart(), DateDiff() and DateAdd(). The following function illustrates each of these functons. Public Function ShowDates() '================================================================== ' Grover Park Consulting ShowDates ' George R. Hepworth 10/24/2016 07:47 AM ' This function illustrates some basic date functions ' Error handling not included ' ' Declare the variables you need to manage dates '================================================================== Dim dtToday As Date Dim dtTomorrow As Date Dim dtChristmasday As Date Dim intDaysLefttoChristmas As Integer 'Set the value of a variable called dtToday to the current date. dtToday = Date ' Use the DatePart function to determine the current year. ' The year is concatenated with the month and day (12/25/ to set the value of the variable ' to Christmas of the current year. ' use CDate to convert the result to a date datatype ' If you fully qualify all parts of the DatePart Function, Intellisense supplies appropriate constants. ' The last two arguments --FirstDayofWeek and FirstWeekofYear -- are optional ' and default to the constants shown in this example if not supplied dtChristmasday = CDate("12/25/" & DatePart(Interval:="yyyy", Date:=dtToday, FirstDayofWeek:=vbUseSystemDayOfWeek, FirstWeekofYear:=vbUseSystem)) ' An alternative is to use the Year() function instead of DatePart ' Comment out the preceding line and uncomment this line to test ' dtChristmasday = CDate("12/25/" & Year(Date:=dtToday)) 'Use the DateAdd function to add 1 day to the current date. dtTomorrow = DateAdd(Interval:="d", Number:=1, Date:=dtToday) 'Use the DateDiff function to calculate the number of days between tomorrow and Christmas intDaysLefttoChristmas = DateDiff(Interval:="d", Date1:=dtToday, date2:=dtChristmasday, FirstDayofWeek:=vbUseSystemDayOfWeek, FirstWeekofYear:=vbUseSystem) MsgBox Prompt:="Today is: " & dtToday & vbCrLf _ & "Tomorrow is: " & dtTomorrow & vbCrLf _ & "Christmas is " & intDaysLefttoChristmas & " days after tomorrow.", _ Buttons:=vbOKOnly + vbInformation, _ Title:="Can You Wait That Long?" End Function