Date Stuff–Using the Functions

This post is the second in a 3-part series on Date functions in Access. If you have not read the first post (Date Stuff – Functions), it might be worth while to do so.

1. Date Stuff – Functions
2. Date Stuff – Using the functions (this post)
3. Date Stuff – Useful Examples

To see working examples, please see the following sample: DateStuff.mdb on my website: www.RogersAccessLibrary.com.

Using Date Functions

Date functions (as indeed any built in or user defined functions) can be used in several ways within the database application:

• Control Source
• Queries
• UDF (User Defined Functions)
• Default Value of Field

Date Functions in the Control Source

Perhaps the easiest way to use date functions is directly in the ControlSource property of a form or report control.  What is a control?  Control is a general name for objects on forms or reports like textboxes, comboboxes, listboxes, buttons, and so forth.  Most of the time, this is going to be a textbox,

The ControlSource property determines where the control (textbox) will get it's value.  On a "bound" control, this is usually a field from the underlying RecordSource (table or query) of the form or report.  However, controls can also be "unbound", which means it is not tied to a particular field.  In this case, you can use the Control Source to display any number of things.  An unbound control will ONLY display values.  They won't be saved to the underlying table.

To use a date function in the ControlSource of a textbox, simply preface it with an equal sign (=), much like you do with a function in an Excel cell. 

For instance to display the current date in a textbox:

=Date()

To display the someone's age, you can use the DateDiff function to find the difference between the current date and their birth date which would have to be stored in a field like DOB.

=DateDiff("yyyy",[DOB],Date())

Notice that when the Date() function is used, you MUST use the parentheses after it.  If you don't, Access will assume it's a field named [Date] and it will produce an error.

Date Functions in Queries

Instead of calculating the date at the form level, you can also use the date functions in the query underlying the form or report.

To do the same age calculation in the Query Builder, I could add a calculated field like this:

Age: DateDiff("yyyy", [DOB], Date())

In SQL View, it would look like this:

SELECT FirstName, LastName, DOB, DateDiff("yyyy",[DOB],Date()) AS Age
FROM BirthDates;

Now, suppose instead of just listing everyone's ages, I want to select just those records for people between 12 and 45.  I can add a criteria to my calculated field like this:

SELECT FirstName, LastName, DOB, DateDiff("yyyy",[DOB],Date()) AS Age
FROM BirthDates
WHERE DateDiff("yyyy",[DOB],Date()) Between 12 And 45;

Of course, that's not the only way to do this.  I could also use the DateAdd function in the criteria of DOB, like this:

SELECT FirstName, LastName, DOB
FROM BirthDates
WHERE DOB Between DateAdd("yyyy",-12,Date()) And DateAdd("yyyy",-45,Date())

Here, I'm using the DateAdd with a negative value to subtract 12 and 45 years from the current date to yield the correct date range.

Date Functions in User Defined Functions (UDF)

Date functions can also be used in VBA code. If I use a particular date conversion frequently, I can create a User Defined Function (UDF) to calculate the value, which saves me from typing in the formula over and over.

For instance, if I need to calculate the age in many places, I could create the following function:

Function Age(DOB As Date) As String
    Age = (DateDiff("yyyy", [DOB], Date))
End Function

Notice that the current date function Date() is NOT followed by parentheses as it is in the ControlSource or in a Query.

This function can then be called from the ControlSource of a textbox:

or in a Query:

SELECT FirstName, LastName, DOB, age([DOB]) AS AgeInYears
FROM BirthDates;

In order to be used globally, that is, throughout the application, this function must be in a General Module rather than a module behind a form.  If it is in a module behind a form, it can ONLY be used in that form.

Date () in Default Value of Field

Lastly, you can use date functions in the Default Value of a field.  This can be useful if you want to enter a value when the record is created.  For instance, if you want the date and time the record was created, you can put =Date() in the Default Value property of a field.

Which will result in a the current date and time in the new record:

It is worth noting, however that this records the date and time that the record was CREATED, not when it was SAVED.  If the difference is important, you need to use a different method to calculate the date and time.  When using the Date() function, you usually don't have any problem.  However, the Now() function WILL store the wrong value for the time.  I'll discuss this more fully in a later post.

Similarly, if I wanted to create a date 30 days from the time the record is created, I could add:

=DateAdd("d",30,Date())

to the Default Value of the Net30 field:

There are some limitations, however. 

You can't use a User Defined Function in the Default Value property.  They can only be the built-in functions. You also can't reference another field in the table.

So the following WILL NOT work in the Default Value:

=DateDiff("yyyy", [DOB], Date)
=Age([DOB])

Next time, in Date Stuff – Useful Examples, I'll show some examples of date functions to do useful things like: 1) finding the first and last days of the current, previous, and next weeks; the first and last days of the current, previous, and next months; and so forth.