Date Functions in
MS Access
~ How They Can Be Used To Help You
This month we will take a slightly different approach to this article. We
will dispense with most of the talk about how things in Access work and show
you a number of examples of how you can apply them in real life. We will
start with a brief explanation of the functions and then jump to the
examples.
Before we begin however, there is one caveat. There are two similar
functions, Date() and Now(). While Now() will give you the exact time, it
can throw off some of the other search criteria, most notably the Between
criteria. This is due to the default time in a date being midnight;
therefore when you do a Between search with just dates on a field that uses
Now(), you will search on everything between midnight on your beginning date
and midnight (00:00:00) on your end date. This means that any time
<I>after</I> midnight (1:00 am, 8:15 pm, etc.) will not be searched.
The Date functions include: Date, DateAdd, DateDiff, and DatePart. All of
these are very useful in helping build and define your database and how your
data is handled.
Date()
DateAdd()
DateDiff()
DatePart()
Forget the Explanation Give me Examples!
Date()
The Date() function, like many things in Access, does pretty much what you
expect it to do. It returns the current date. There is a Now() variation of
Date(). The only difference is that it returns the current date <I>and
time</I>, rather than just the date. You will find this function invaluable
as you build your own database. It can be used as a default value in a field
on a form or as criteria in a query. There are no parameters to worry about
with this function.
DateAdd(interval,number,date)
The DateAdd() function adds a specified period of time to the date given. It
can be used on specific dates or in conjunction with other fields and
criteria to work dynamically. There are three criteria to use with the
DateAdd() function. The first is interval. This allows you to specify how
much time you want to add to the date in question. You can specify any
interval from seconds to years. This gives you great flexibility in how you
can use it. The second parameter is number. This is simply the number of
intervals that you specified. The final parameter is the date you wish to
have appended. As an added benefit, the function recognizes negative numbers
so can actually go back in time as well as move you forward.
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function allows you to determine the difference between two
designated dates in the interval of your choice. The parameters include:
interval, date1, date2 and two optional parameters allowing you to determine
the beginning of the week and year. This tool again gives you great
flexibility in how you use this function.
DatePart(interval, date[,firstdayofweek[, firstweekofyear]])
This is probably the most misunderstood but most useful of the date
functions. It allows you to extract any part of a given date to use
independent of the date itself. There are numerous uses for this function
and you will see many of them in the examples area. There are two parameters
to the DatePart function, interval and date. The interval is the time
interval you wish to extract and the date is obviously the date you are
extracting from.
Enough of the Explanations Get to the Examples Already!
As promised here are a number of examples for you to peruse and try out
for yourself. As an added bonus, many of these will also work in Excel!
How to Determine Age:
=(DateDiff(d, Birthdate, Date()))/365.25
This will return a value for age in years, with a number of decimal points.
How to dynamically Set a Criteria to Last Month End:
Date()-datepart(d,Date())
Notice the use of multiple date functions. You can use as many as are
needed, just be aware of what kind of impact it may have on performance.
Dynamically calculate One week from today:
DateAdd(w,1,Date())
DateAdd(d,7,Date())
Notice that there are a number of ways at arriving at the same answer.
Set criteria to return all values from the previous month:
There are a few ways to do this but I have found the most efficient to be:
Create two new fields not shown as:
EffectiveM: Datepart(m, effective)
EffectiveY: Datepart(yyyy, effective)
This returns the month and year of the field called effective. Next set your
criteria as follows:
Under EffectiveM, IIF(datepart(m, date())=1, 12, Datepart(m,Date())-1)
Under EffectiveY, IIF(datepart("m", Date())=1, Datepart("yyyy", Date())-1,
Datepart("yyyy", Date()))
Notice the extensive use of the datepart function in conjunction with the
date function. This set of formulae and criteria will return all values from
the previous month and accounts for the rollover in year. As I said, there
are other ways of doing this, this particular way works best for me, and
others may have a preference of their own!
Set criteria to return all values from the current month:
EffectiveM: Datepart(m, effective)
EffectiveY: Datepart(yyyy, effective)
This returns the month and year of the field called effective. Next set your
criteria as follows:
Under EffectiveM, Datepart(m,Date())
Under EffectiveY, Datepart(yyyy, Date())
To Calculate a Future Date in Time Dynamically:
DestroyDate: DateAdd(yyyy, [Retention],[EndDate])
Note that this will give you a future date based on two fields. This example
comes from a retention database that allows the user to specify the desired
retention period for the item being stored. This allows you greater
flexibility as the same function can be used for various destruction
periods. If you chose, you could set the interval as a field and allow your
users to determine the entire retention period on their own. This can be
extremely useful in an environment that you allow your users to purge data
on their own.
As part two of this article, I am going to ask that you send in your
favorite creative use of the Date functions and we will share them with
the readers in the next issue. Feel free to send in your favorites or if you
are stuck on determining a specific timeframe, send that in too. We may just
be able to help you. I would also like to hear from anyone who has a
specific topic that they would like to have covered.
James
James La Borde works
in the computer department at a Credit Union,
where he uses Access, SQL Server, VBA, and ODBC daily. He also
teaches online Access classes at
Eclectic
Academy.
,
