Say thanks for these Date & Time Functions.
Visit our e-book
project site.
Visit our e-book
project site.
The dates and times we input and see displayed in Access are not in the same format as what is stored for dates and times. They are stored as double precision numbers. The integer part is a numeric representation of only the date, and is called a Julian, or Serial, date. To do this, Access converts the date to an offset from a fixed point in time (12/30/1899), and all dates are stored as the number of days since this date. Thus 8/27/1999 is stored as 36399, meaning 36,399 days since 12/30/1899.
Time represents the decimal part of the double that Access stores for date and time. Since adding 1 to a stored date/time represents 1 day or 24 hours, each hour is stored as .041666, or 1/24 of a day. In Access all times are stored as a fraction of a day. Each hour is 1/24 of a day, each minute 1/1440, each second 1/86400. So 3:00 is stored as .125 (or 1/8 of a day), and 16:00 is stored as 0.666, (or 2/3 of a day). Conversely, 0.2 represents 4:48 hours (1/5 of a day), and so on.
Fortunately, there are numerous functions for handling dates and times in VBA.
| Function | Description |
|---|---|
| Now | Current date and time. Example: 7/5/00 3:16:38 PM returned by Now |
| Date | Current date only. Example: 7/5/00 returned by Date |
| Time | Current time only. Example: 3:12:38 PM returned by Time |
| Timer | Number of seconds since midnight. Example: 3:16:38 PM returned by Timer See other examples below this table. |
| TimeValue() | Time part of argument. Example: 3:16:38 PM returned by TimeValue(Now) |
| DateValue() | Date part of argument (excellent for ordering by date) Example: SELECT * from tblPeople ORDER BY DateValue(Review) |
| DateSerial() | Date part of three arguments: year, month, day Example: HAVING InvoiceDate <= DateSerial(Year(Now), Month(Now)-1, Day(Now)) DateSerial handles January correctly in the above example |
| DatePart() | Returns a portion of the date. Year example: 2000 returned by DatePart('yyyy', Date) Month example: 10 returned by DatePart('m', #10/11/2001#) Week of year example: 41 returned by DatePart('ww', #10/11/2001#) Day of Week example: Monday returned by DatePart('dddd', #6/3/2002#) Quarter example: 4 returned by DatePart('q', #10/11/2001#) |
| Year() | Returns the year portion of the date argument. Also see DatePart() above. |
| Month() | Returns the month portion of the date argument. Also see DatePart() above. |
| Day() | Returns the day portion of the date argument. Also see DatePart() above. |
| MonthName() | Used to format month names. July returned by MonthName(Month(Date)) |
| WeekdayName() | Used to format day names. Wednesday returned by WeekdayName(Weekday(Date)) |
| Today() | Current date only; used in Excel, not available in Access |
| DateDiff() | Returns the difference in dates. Days example: -656 returned by DateDiff("d", #10/11/2001#, #12/25/1999#) Months example: 1 returned by DateDiff("m", #8/10/2000#, #9/14/2000#) Days example: 0 returned by DateDiff("m", date1, date2) 0 is returned above only if the two dates have same month and year |
| DateAdd() | Add and subtract dates. 10/11/2002 returned by DateAdd("yyyy", 1, #10/11/2001#)) Today's date + 30 days returned by DateAdd("d", 30, Date) The date 45 days ago returned by DateAdd("d", -45, Date) To find Monday of a week: DateAdd("d",-WeekDay(Date)+2,Date) |
| Format() | Very useful for formatting dates. Examples: Wed, 5-July-2000 returned by Format(Date,"ddd, d-mmmm-yyyy") 5-Jul-00 returned by Format(Date,"d-mmm-yy") |
You may want to know the last time a record was changed. To accomplish this you can add a date stamp field (LastUpdated) to a table, then set the field's Default Value to Now, and add the field to your form but make it invisible. Access will enter the current date whenever you add a new record using the form. To change the LastUpdated field to the current date when you alter an existing record, use the form's Before Update property and set LastUpdated to Now.
You can use the Timer function to time operations and events in an Access application by placing the Timer function immediately before and after the operation you want to time. Timer also can be used to pause your application for a given span of time. See Help. The following example uses the name of a query as its argument, then calculates how long it takes the query to run.
Sub QueryTimer (strQueryName As String)
Dim sngStart As Single, sngEnd As Single
Dim sngElapsed As Single
sngStart = Timer ' Get start time.
DoCmd.OpenQuery strQueryName, acNormal ' Run query.
sngEnd = Timer ' Get end time.
sngElapsed = Format(sngEnd - sngStart, "Fixed")' Elapsed time.
MsgBox ("The query " & strQueryName & " took " & sngElapsed _
& " seconds to run.")
End Sub
The code below will return a semi-monthly integer, which is an integer from 1 to 24 that represents a semi-monthly period in the year, beginning with January 1. Use the function to determine the semi-monthly period of any date.
Public Function Semi(X As Date) As Variant
If Day(X) > 15 Then Semi = Month(X) * 2 Else Semi = Month(X) * 2 - 1
End Function
