MS Access is a very versatile data handling package. It has numerous inbuilt function which enable formulating complex queries. The date functions of Microsoft Access include Date, DateAdd, DateDiff, and DatePart. They are very helpful in data handling.
This function returns the current system date. It has no parameters and can be used in a form field or as a criteria in a query.
This function can be used to add or subtract a specified interval to any date. It returns a Variant containing the resultant date. For instance, if you want to get the date 45 days from now, you can use the DateAdd function.This function will not return an invalid date.
DateAdd Syntax:
DateAdd(period, increment, date)
<period> | This required parameter denotes the interval by which you want
to increase the date. It is a string expression. Any period from
seconds to years can be specified. This parameter can have the
following variations:
|
||||||||||||||||||||
<increment> | It is the number of periods you wish to add. A positive number gives you a date in the future and a negative is used for returning a past date. | ||||||||||||||||||||
<date> | This is the date in question to which the <period> is to be applied. |
DateAdd("m", 1, "30-Jan-99") - It will return 28-Feb-99.
DateAdd("w",1,Date()) - This will return a date one week from the current date. Note that the same date can be retrieved by the following:-
DateAdd("d",7,Date())
Variables can also be used as parameters in this function. Newdate = DateAdd("d",[addperiod],[oldDate])
This function returns the differential interval between two dates. You can choose the type of interval in which you want the difference.
DateDiff Syntax:
DateDiff(period, firstdate, seconddate[, firstdayofweek[, firstweekofyear]])
<period> | This argument specifies the type of interval in which you require
the date difference. This parameter can have the following variations:
|
||||||||||||||||||||||||
<firstdate> | This is one of the two required dates. | ||||||||||||||||||||||||
<seconddate> | This is the second of two required dates. | ||||||||||||||||||||||||
<firstdayofweek> | This optional argument specifies the first day of the week.
if none is given, Sunday is the default. It has the following
settings:
|
||||||||||||||||||||||||
<firstweekofyear> | This optional argument specifies the first week of the year.
If none is given, the week of January 1 is the default. It has
the following settings:
|
If the <firstdate> is later than the <seconddate>, this function returns a negative value.
If <firstdate> or <seconddate> is enclosed in double quotation marks (" "), and the year is omitted, the current year is put in your code each time the <firstdate> or <seconddate> expression is evaluated. Thus, the code can be year independent and can be used in separate years.
DateDiff("ww", "1-1", Now()) - This example returns the number of weeks elapsed since January 1. note that the year is not mentioned in the specified date.
(DateDiff("d", joindate, Date()))/365.25 - If joindate gives the date of joining, this function will return the number of years the employee has worked.
This function returns a specified portion of a given date.
DatePart Syntax:
DatePart(period, date[, firstdayofweek[, firstweekofyear]])
<period> | This argument specifies the type of interval in which you require
the date difference. This parameter can have the following variations:
|
||||||||||||||||||||||||
<date> | This is the required date. | ||||||||||||||||||||||||
<firstdayofweek> | This optional argument specifies the first day of the week.
if none is given, Sunday is the default. It has the following
settings:
|
||||||||||||||||||||||||
<firstweekofyear> | This optional argument specifies the first week of the year.
If none is given, the week of January 1 is the default. It has
the following settings:
|
DatePart("q",[newdate]) - This will extract the quarter of the argument [newdate].
DatePart ('m', #11/12/2006#) would return 12
All of the date functions can be used independently and in conjunction to form powerful codes for handling data.