Access has a large number of built-in functions, including standard math and financial functions, plus a number of functions that are special to Access. Quite a number of these special functions are for handling dates and times. Dates/Times are so messy to calculate with that we need a separate lesson for them.
Examples of special functions:
Sqr([Length]) returns the square root of the value in the field Length
Round([FinalValue],3) rounds the amount in the field FinalValue to show 3 digits to the right of the decimal
Now() returns the current date and time
DatePart ("yyyy", [EndDate]) returns the year part of the date in the field EndDate.
Format(Now(),"mmm") return the current month as a three character abbreviation.
In Access, a date/time is actually a number, based on the number of days and fractions of a day since midnight December 30, 1899. Dates before that day are stored as negative numbers. Access can handle dates from January 1, 100 AD through Dec. 31, 9999 AD. Should be enough for most uses!
Examples of dates:
Dec. 31, 1899 = 0
Jan. 1, 1900 = 1
Nov. 30, 1899 = -30
Examples of dates with times:
The date is the whole number part and the time is the decimal
fraction of a day.
Dec. 30, 2000 6:00 am=
36,890.2500000000
July 15, 1941 3:36:00 PM =
15,172.6500000000,
We are all very happy to let Access do the translation so that we can work with normal looking dates and times!
All dates include time: Every date also has a time as the decimal part of the number. If
you do not set a specific time, Access interprets the time as midnight,
since the decimal value for midnight is .0000000000.
Date/Time as text: If
you choose the Text data type for a field that will hold a date or time, you
will not be able to calculate with the field or switch its display
format, like changing from 2/5/2004 to February 5, 2004.
Calculating with dates and times can be complicated. Access provides a number of helpful functions. These functions often use one of several text strings for the various time intervals that we are most often interested in.
Intervals- Values you can use for interval in a function (must include the quotes):
Date Part: Pull out part of a Date/Time
General Form: DatePart(interval, date) - interval is a text string from the list above for the part of the date you want to see and date is a field name or a calculated value that results in a date/time.
There are two optional parts to this function that you are not likely to use very often:
DatePart(interval, date [,firstweekday [,firstweek)
firstweekday is the number of the day of the week that you want to use as the first day. The default is Sunday.
firstweek is the week number that you want to use as the first week of the year. The default value is the week that contains January 1. Businesses or governments that have their own "business year" or "tax year" would need to change what the first week of their year is.Examples:
DatePart("yyyy",[Birthdate]) will produce the year part of the Birthdate value.
DatePart("m",[Birthdate]) will produce the month part of the Birthdate value.
DatePart("d",Now()+30) will produce the day that is 30 days from today
DatePart("w",Now(),2) will produce the number of the day of the week, starting with Monday as day 1.
DateAdd: Add to or subtract interval(s) from a Date/Time
General Form: DateAdd(interval, number, date)- Takes the date and adds the given number of intervals to it. To subtract, use a negative number of intervals. The function is still DateAdd!
Examples:
DateAdd("m",2,[MyDate]) will add 2 months to the value in MyDate.
DateAdd("yyyy",-10,[MyDate]) will subtract 10 years from the value in MyDate.
DateDiff: Difference between two dates
General Form: DateDiff(interval, date1, date2) - Finds the number of intervals between date1 and date2. The value is positive if date2 is later than date1 and is negative date1 is the later date.
Like the DatePart function, there are two optional parts to this function:
DateDiff(interval, date1, date2 [,firstweekday [,firstweek)
firstweekday is the number of the day of the week that you want to use as the first day. The default is Sunday.
firstweek is the week number that you want to use as the first week of the year. The default value is the week that contains January 1.Examples:
DateDiff("yyyy", [SoldDate],Now()) will show the number of years between today and the value in SoldDate.
DateDiff("d",[PurchaseDate], [ShippedDate]) finds the number of days between the two dates.
DateValue: Turn a text string date into a number
When a field for dates is set up as text and later you want to
calculate with that date, you need a function to turn the text into a
number.
General Form: DateValue(date) - returns the number that represents the text string date, like "October 14, 2000".
Examples:
DateValue("Sept. 11, 2001") returns a value displayed as 9/11/2001.
DateValue([DateGraduated]) returns the value displayed as 5/5/1999 when the field has the text value "May 5, 1999".
Format: Pattern for display of a Date/Time
The Format function allows you to choose a standard pattern or create
your own pattern for how to display a Date/Time value. Don't
confuse it with the DatePart function, however. The DatePart function can only pull out one part of the date/time value, like the month or
day or year or hour.
General Form: Format(datetime, pattern) - takes the datetime value and formats it like pattern.
Examples:
Format(Now(),"dddd, mmm d yyyy") returns "Tuesday, Mar 7 2014" if the current date is 03/07/2014. Only the punctuation that you include in the pattern will show up.
Format(Now(),"h:m:s") return the time portion, like 13:23:45 if the current time is 1:23:45 pm.You can also use the standard date/time formats for the pattern:
Format Pattern Name: | Produces: |
---|---|
General Date | 3/8/2006 10:39:59 AM |
Short Date | 3/8/2006 |
Medium Date | 08-Mar-06 |
Long Date | Wednesday, March 08, 2006 |
Short Time | 10:39 |
Medium Time | 10:39 AM |
Long Time | 10:39:59 AM |
It is easy to be off by a year when trying to calculate someone's age or an anniversary of some sort on a computer. An age/anniversary depends on whether or not the one for the latest year has occurred yet. You cannot just find the difference between the years. You must consider the month and day and compare them with today. So confusing!
Microsoft suggests a messy-looking but sneaky and effective expression to handle this issue. It combines 3 functions: DateDiff, Format, and Int.
Age: DateDiff("yyyy", [Birthdate], Now())+ Int( Format(Now(), "mmdd") < Format( [Birthdate], "mmdd") )
Let's break this mess apart to see how it works.
Age will be the number of years from the DateDiff calculation plus 0, if we have passed the birth day already, or plus negative 1 (-1), if the birth day is still in the future.
DateDiff("yyyy", [Birthdate], Now()) calculates the difference between the year of the birth date and the current year. This is all you would need if you KNEW that the birthday for this year had already passed.
Format(Now(), "mmdd") returns the month and day for today's date.
Format( [Birthdate], "mmdd") returns the month and day for the birth date
Format(Now(), "mmdd") < Format( [Birthdate], "mmdd") asks if the month and day for today is before the month and day in the birth date. So, if today is March 5, 2014 and the birth date is May 25, 1972, we are looking at the question "Is March 5 < May 25", which is True.
Int(expression) The Int function returns the integer part of a number. In this case we are using the Int function to get a number out of a logical comparison. How does that work?
A weird feature of programming languages is that True and False have to actually be number values for a computer to work with them. A False expression evaluates to 0. A True expression evaluates to -1. (The reason comes from the way computers store numbers and is a bit technical.)
So when today's date is before the birthday, the Int part is -1 and the full expression for Age adds -1 to the DateDiff. TaDa! We have the correct age, as of today! As soon as we get to May 25, the Int part of the expression evaluates to 0 and the DateDiff result is the true age.
![]() |
Step-by-Step: Calculated Values - Dates |
![]() |
What you will learn: | to write a simple calculation for a new field to use the Format function to use the Expression Builder to get Help for a function from Expression Builder to use the DateDiff function to correct a date calculation based on the current date/time to use the DateAdd function to group and count records to use the DatePart function |
Start with: , Projects database from previous lesson open.
Do
not panic when your results are different from the illustrations. You
will be using the Now() function, which produces the current date
and time. Therefore, you will see different results every day!
The Staff table in the Projects database includes the date the person was hired. You will create a calculated field to show how many years this person has been working for World Travel Inc. First, you will use a simple expression. Since this value will be wrong during part of the year, you will have to use a more complex expression to get the number right. <sigh>
If necessary, open the Projects database (projects2-Lastname-Firstname.accdb ) from your Class disk.
Select
Right click in a blank area of the Navigation Pane
and select
A dialog appears for you to use to name the copied query.
Name the new query QStaff-LengthOfService
Open the new query in Query Design View.
Drag the DateHired field to the first blank column at the right in the grid.
You may need to scroll the columns to find the blank columns.
In the next blank column, create a new field:
LengthOfService: Now()-[DateHired]
This expression subtracts the date/time hired from the current date/time.
TAB out of the field.
Run the query.
The column LengthOfService shows hash marks, ######## because the column is too narrow to show the value.
Double click on the right edge of the column heading to widen the column.
Unexpected values! The LengthOfService field shows a bunch of decimal numbers! What do these values mean?
[Your numbers will be different from the illustration since the Now function uses the current date and time.]
The integer part of each value (to the left of the decimal) is the number of days since the person was hired. The decimal part (to the right of the decimal) is the fraction of a day.
Because the DateHired values do not have a time included, Access assume that the time was midnight, .000000000. That is why the decimal part is the same for all the values.
Stretch Your Brain: Can you figure out what day
and time the illustration was captured based on the LengthOfService
values??
Save the
query.
[QStaff-LengthOfService]
You need to use the Format function to get the number of years instead of the messy number for LengthOfService. Remember that the values change as time marches on during the day!
Syntax for Format: Format (datetime, pattern)
Switch back to Query Design View.
Edit the calculated field to add some new parts to subtract the year hired from the current year:
LengthOfService: Format(Now(),"yyyy")-Format([DateHired],"yyyy")
The
Format function pulls just part of a date/time value for the expression or field.
The "yyyy"
tells the Format function that you want just the year part.
So now the new field is calculated by finding the difference between two years.
Use the Zoom
window so you can see what you are doing. Increase the font size for the Zoom window if you wish.
Run the query.
Better!!
These values look like years.
Remember, your values in the LengthOfService column will be different from the illustration!
Access provides a special function for finding the difference between two dates. Perhaps it will be less complicated!
While the Zoom window gives you more space to see what you are typing, the Expression Builder can show you the syntax for functions. Very helpful!
Syntax for DateDiff: DateDiff(interval, date1, date2)
interval is a code for what you want to count, for example the
number of years, months, days, or quarters.
If date2 is later than date1, the difference will be a positive number.
Switch back to Query Design View.
Right
click the LengthOfService field and from
the popup menu select .
The Expression Builder window opens and shows the current expression.
The left column in the dialog shows the objects and functions you might want to use in
an expression. The middle column shows the choices or parts of the item
selected in the left column. The right column shows the choices for what
you selected in the middle column.
Experiment by choosing various items in the first column and middle
column. Do you see how these columns are working??
Delete everything in the expression at the top
of the dialog except the field name, LengthOfService:
Double-click Functions in the left column to
expand the list.
Click on Built-in Functions.
A list appears
in the middle window of the categories of functions that Access already
knows.
Click on Date/Time in the middle column. A list of functions appears in the right column.
Click on DateDiff to select it.
At the
bottom of the window appears the syntax of this function.
What if you don't remember what the parts of the function represent? Help
is available!
Click the definition in the bottom of the window.
TaDa! A VERY complete explanation of the function
appears. You must be online for Access 20913 and 2016.
Visual Basic: This Help info is actually from the Help articles for Visual
Basic, which is a programming language. The syntax information is what you need to use the function in Expression Builder.
Double-click DateDiff in the right column. The function is inserted into the existing expression, with placeholders for all the possible parts.
Next you must replace or delete those placeholders.
Edit the expression to read:
LengthOfService: DateDiff ("yyyy", [DateHired], Now())
where the interval is "yyyy", date1 is [DateHired], and date2 is Now().
Click on OK to accept the new expression and
close the Expression Builder window.
Run the query.
The values from this calculation are the same as before, as they should
be.
The expression using DateDiff is a bit shorter than the previous version
using Format. That's about the only difference.
Which method do you think would be easier to use in the future?
In fact, the hardest part for both functions is remembering the order of
the parts! The Expression Builder handles that for you!
(Your values will be different than those in the
illustration since Now() is a different date for you. The dates should all be different by the same number of years, of course.)
Your current expression for LengthOfService gives you a number of years that is guaranteed to be true only at the end of the year! If the anniversary date has not arrived yet, the number is off by 1 year.
To show the length of service as of today, you must adjust your expression to subtract a year if the anniversary date has not yet arrived. Happily, Microsoft provides a sample expression, discussed above.
Syntax for expression to calculate the number of years between
the current date and another date:
NumberOfYears: DateDiff("yyyy", <date>, Now()) +
Int(Format(Now(), "mmdd") < Format(<date>, "mmdd") ) where <date>
is some calendar date or expression that produces a calendar date.
What does the end of that expression do? The Format parts produce the number for the day of the year. The Int function produces a number based on whether the inequality < is True or False. The value will be 0 if False and -1 if True.
Switch back to Query Design View.
Edit the LengthOfService field to add a new part at the end:
LengthOfService: DateDiff("yyyy",[DateHired],Now())+Int(Format(Now(),"mmdd")<Format([DateHired],"mmdd"))
This long expression finds the difference between the years of the two dates in the DateDiff part like before.
Then the inequality < compares the day of the year for today to the day of the year for DateHired.
If today is earlier in the year than the day part of DateHired, the expression will be True and the value for Int will be -1. So 1 will be subtracted from the DateDiff and give us the correct number of years today, when we have not yet gotten to the anniversary of DateHired.
If today is the anniversary for DateHired or if we are past that date, the value of Int is 0. So the original DateDiff number was correct and no change is needed.
Whew! What a lot of thinking that took!
Run the query.
Now the LengthOfService is reduced by 1 for
those dates that have not yet occurred.
(Your numbers will be different! I captured the image on January 1, 2013. So ALL anniversaries of the DateHired were in the future.)
World Travel Inc. holds a special party for employees when they reach 10, 15, or 20 years of service. You will write an expression that will calculate everyone's 10 year anniversary by using the DateAdd function. This function does not add two dates together! Instead, it adds a certain number of intervals to a date. You can add so many years or months or days or hours, etc.
Syntax for DateAdd: DateAdd(interval, number, date)
Switch back to Query Design View.
Create a new calculated field:
10Year:DateAdd("yyyy",10,[DateHired])
This expression says to add 10 years to DateHired.
Sort the new field Ascending and remove all other
sorts.
Remember to scroll the field columns to check the ones out of sight to the left.
Run the query.
You can see from the column 10Year that at least some anniversaries have already
occurred. All will have occurred if you are doing this after May 25, 2014!
The illustration was captured from Access 2016, April 30, 2016.
Switch back to Query Design View.
Create two new calculated fields, named 15Year and 20Year, that calculate the 15 and 20 year anniversaries.
Only the number of intervals has to change.
The datasheet shows the two new columns.
Suppose you want to know how many employees will have a 10-year anniversary in each year. You can use the Total row to count them. Just remember that in a Totals query you want to show only the field(s) that you are grouping by and the column that you are using to count.
Run the query.
Hmmm. Why are there two 10Year values in 2005 on separate rows?
The grouping is on the whole date, not just the year. Whoops. There actually were two employees who were hired on the same date or we would not have had anything but 1 in the Count column. This is not quite what we wanted to see. You must look at results very carefully to spot this kind of issue!
You need to make the query look only at the year part of the date. The DatePart function can come to the rescue!
Syntax for DatePart: DatePart(interval, date)
Edit the expression for the 10Year field to read:
10Year: DatePart("yyyy",DateAdd("yyyy",10,[DateHired]))
This
changes the expression to show just the year portion of the date that DateAdd
comes up with.