Working with dates
Whenever you develop a commercial application, there is absolutely no way that you can get by without using date fields. There are Birth dates, Hire dates, Delivery dates, Order dates, and so on, and so on ....
In Access, a date or time constant should be identified with # ... #, as in:
... [m_videorelease] = #2001-01-01#;
To begin with, know that you can do some calculations with dates as you do with numbers.
#2003-01-31# - #2003-01-01# will return 30, the number of days between the 2 dates.
#2003-01-01# + 3 will return #2001-01-04# because a numeric constant is always taken to mean days.
When using the comparison operators, > #date1# is taken to mean later than or after and < #date1# is taken to mean earlier than or before.
The smallest date value refers to the earliest or oldest date. The person with the smallest date-of-birth is the oldest. This sometimes comes-in handy because you can sort people by age without having to actually calculate age: to get a list in ascending age order, sort on date-of-birth descending.
In the criteria BETWEEN #date1# AND #date2# sets a date between date1 and date2, inclusive.
To work with date fields we'll use the Date and Time functions that Access supplies. There are dozens of built-in functions that will allow us to manipulate dates and times in just about any way that you can imagine.
The function that will probably be used more than any other: Now( ) returns the current date from the system clock.
In Access and SQL, one of the most useful functions is called: DateDiff( )
DateDiff('interval', #date1#, #date2#) returns the time difference between date1 and date2, expressed in interval units which could be: days, months, years, weeks or hours.
The interval is specified as: 'd' for days, 'w' for weeks, 'm' for months and 'yyyy' for years.
For example:
Datediff('d', #2003-01-01#, now()) returns the number of days between January 1st, 2003 and today.
Datediff('m', p_StartDate, p_EndDate) returns the number of months between start date and end date, in this case, for a project.
If the result displays too many numbers after the decimal, use the ROUND(number, digits) function to display the number rounded to 'digits' positions after the decimal:
ROUND(Datediff('m', p_StartDate, p_EndDate), 2).
In theory, Datediff('yyyy', c_BirthDate, now()) returns the customer's age, expressed in years. In practice however, you will find that it works or doesn't work depending on whether the person has had his birthday yet this year or not. Usually it doesn'y work very well.
To calculate the exact age, the following formula is quite accurate. There may be a small variation of a day or so once in a while.
INT(Datediff('d', c_BirthDate, now())/365.25)
Calculate the number of days between birth and now and divide by the exact number of days in a year, which, as you know, is 365.25 and not 365. That takes leap years into account.
The Integer function, INT( ) , truncates the result so that 25.9 becomes 25, for example; the person is 25 years old until the day she turns 26; after the age of 5, you rarely hear people say that they are 25 and a half years old.
Using update queries allows many ways of manipulating dates in Access. It is often easiest to create the function by using the “build” facility. The following examples do not show all the possibilities and may miss some optional parameters, use Access Help for full details. More complex functions can be creating by sequencing a set of simpler queries into a macro. The functions were tested in Access XP but most will work in Access 97.
ADDING TO A DATE
DateAdd(“d”,5,original_date)
Would add 5 days to the original date January 30th becomes February 4th
The parameter d can be replaced by w for weeks, m for months or yyyy for years.
You can add a negative number to subtract from a date.
The DateAdd function returns a date after which a certain time/date interval has been added.
The syntax for the DateAdd function is:
DateAdd ( interval, number, date )
interval is the time/date interval that you wish to add. It can be one of the following values:
Value / Explanationyyyy / Year
q / Quarter
m / Month
y / Day of the year
d / Day
w / Weekday
ww / Week
h / Hour
n / Minute
s / Second
number is the number of intervals that you wish to add.
date is the date to which the interval should be added.
For example:
DateAdd ('yyyy', 3, #22/11/2003#) / would return '22/11/2006'DateAdd ('q', 2, #22/11/2003#) / would return '22/05/2004'
DateAdd ('m', 5, #22/11/2003#) / would return '22/04/2004'
DateAdd ('n', 51, #22/11/2003 10:31:58 AM#) / would return '22/11/2003 11:22:58 AM'
DateAdd('yyyy', -1, #22/11/2003#) / would return '22/11/2002'
Visual Basic Code
The DateAdd function can be used in VBA code. For example:
Dim LDate As Date
LDate = DateAdd ("s", 53, #22/11/2003 10:31:58 AM#)
In this example, the variable called LDate would now contain the value of '22/11/2003 10:32:51 AM'.
SQL/Queries
You can also use the DateAdd function in a query.
FINDING DAY OF A WEEK FOR A DATE
Weekday(original_date,1)
returns the day of the week the date falls on, 1 for Sunday…7 for Saturday.
Weekday(14/2/2004,1) = 7 (ie Saturday)
The numbers 1 to 7 can be converted to the corresponding day of the week by using
WeekdayName(number,false,1) (NOT Access 97)
WeekdayName(1, false,1)=Sunday; WeekdayName(7,false,1)=Saturday
(The 1 parameter can be changed to alter which day is the first day of the week, eg change the 1 to 2 to make Monday the first day. If you change false to true the day will be given in an abbreviated form: Sun, Mon…)
HOW LONG IS IT BETWEEN DATES?
DateDiff(“d”,first_date,second_date)
Will tell you how many days between the two dates
The parameter d can be replaced by w for weeks, m for months or yyyy for years.
WHAT DATE IS IT TODAY?
Date()
Will tell you how many days it is since 1 Jan, 1900 so 14 Feb 2004 is 38031
Date$()
Gives the date() value formatted as a date is 14-02-2004
SPLITTING UP A DATE
Year(date)
Will return a whole number representing the year.
Month(date)
Will return a whole number representing the month ( 1 to 12)
MonthName(number) (NOT Access 97)
Will convert a number to the corresponding month so 3 is March
Day(date)
Will return a whole number representing the day of the month ( 1 to 31)