It can sometimes become confusing when working with dates in MS Access. There are a number of functions that manipulate date values and components in MS Access but they are not always readily apparent. The tips below should help you resolve most of the common date issues.
- Date Data Entry Shortcuts
It is not always necessary to enter a literal date value. MS Access offers some keyboard shortcuts that are quicker and reduce the margin for error. Some of these are:
- To enter the current date, click CTRL + ; (semicolon).
- To copy a date from the previous record, click CTRL + ‘ (apostrophe).
- To enter the current time, click CTRL + : (Colon).
- Eliminate Data Data Entry – Sometimes
Sometimes you are dealing with the current date and sometime you not. When you are dealing with the current date, you can eliminate entering any value at all. You can simply set the field’s Default Value property to Now() or Date(). When you enter a new record, the property will enter the system’s current date (and time).
- Now() Versus Date()
The Now() function will return the system’s current date and time. You always get both. If you should only need the current date and not the time, use the Date() function instead. Alternatively, should you just need the current time, use the Time() function. Do not use Now() unless you really mean to store both the date and the time.
- Auditing Changes by Adding an Edit Date
If you have multiple users updating your data, you may want to track who is making changes. You can do so by adding a simple event procedure in the following form to a data entry form’s Dirty event:
Private Sub Form Dirty (Cancel As Integer)
editfield = Now ()
where editfield represents the field that stores the last edit date.
Access fires the form’s Dirty event when the contents of the form changes. This happens even it the user re-enters the same entry. If the user catches the mistake, they can click Esc to cancel the change. However, the user must then click it a second time to delete the newly inserted edit date before leaving the record. Otherwise MS Access will save the edit date.
- Calculating Julian Dates
A Julian date identifies a date by its numeric rank since January 1. This rank tells you how many days fall between a date and January 1 of the same year. For instance, January 1 of any year is 1, February 1 of any year is 32, etc. Leap years complicate matters because dates following February 28 in a leap year are not the same as those in a non-leap year. To calculate a Julian date, you would use the following expression:
JulianDate = date = DateSerial (Year(date), 1, 1) + 1
where date is the date or Date/Time field for which you are calculating a Julian value. This expression will work for leap and non-leap years.
- Using Format() to Display Dates
As a rule you will want to display dates in a specific format without storing the format as part of the date. The Format() furnction will allow you to display individual and combination date components without affecting the stored date value. To display a single component, use the approrpriate format code. As an example, the following expression will return only the day of the month as an integer (1 through 31):
Format (datefield, “d”)
If you would like a leading zero, use dd instead of d.
|ddd||Returns the day of the week as a three letter abbreviation: Mon, Tue, Wed, etc.|
|dddd||Returns the full name of the day of the week: Monday, Tuesday, Wednesday, etc.|
|m||Returns the month as an integer, 1 through 12|
|mmm||Returns the month as a three letter bbreviation: Jan, Feb, Mar, etc.|
|mmmm||Returns the full month name: January February, March, etc.|
|yy||Returns the last two digits of the year: 05, 06, 07, etc.|
|yyyy||Returns a four digit year, 2005, 2006, 2007, etc.|