# Tables & Queries:Calculated Values - Dates

Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

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 lesson just them.

Examples:

• 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.

Where you are:
JegsWorks > Lessons > Databases

Project 4: Forms & Reports

### What is a Date/Time?

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 from 2/5/2004 to February 5, 2004.

### Working with Date/Time Values

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):

• "yyyy" = four-digit year
• "y" =  serial day of the year (1 - 366) (Example: Feb. 15 is day 46.)
• "m" =  month (1 - 12)
• "q" = quarter (1 - 4)
• "ww" = week of the year (1 - 52)
• "w" = day of the week (1 - 7)
• "d" = day of the month (1 - 31)
• "h" = hour (1 - 24)
• "n" = minute (1 - 60)
• "s" = seconds (1 - 60)

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 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.

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 day of the week, starting with Monday as day 1.

General Form: DateAdd(interval, number, date)- Takes the date and adds the given number of intervals to it. A negative number will subtract that many intervals.

Examples:
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 fucntion, 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 the value 9/11/2001.
DateValue([DateGraduated]) returns the value 5/5/1999 when the field has the text value "May 5, 1999".

Format: Pattern for display 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. The Format function can, of course, display only part of 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 2006" if the current date is 03/07/2006. 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: 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

### Special Expression for Calculating an Age/Anniversary:

It is easy to be off by a year when trying to calculate someone's age or an anniversary of some sort. An age/anniversary depends on whether or not the one for this 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 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, 2006 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.
A weird feature of programming 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.  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

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!

### Calculate Length of Service

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>

1. If necessary, open the Projects database from your Class disk.

2. Open the list of queries in the Database Window.

3. Right click on the query QStaff-FullName.

4. Select .

5. Right click in a blank are of the Database Window and select .

6. Name the new query QStaff-LengthOfService

7. Open the new query in Query Design View.

8. Drag the DateHired field to the first blank column at the right in the grid.

9. In the next blank column, create a new field:
LengthOfService: Now()-[DateHired] which subtracts the date/time hired from the current date/time.

10. Run the query.

Unexpected result! 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 number of hours.

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??

11. Save the query. (QStaff-LengthOfService)

### Use Format Function

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!

Syntax for Format: Format (datetime, pattern)

1. Switch back to Query Design View.

2. Edit the calculated field 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.

Use the Zoom window so you can see what you are doing.

3. Run the query.
Better!!

### Use Expression Builder with the DateDiff Function

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.

1. Switch back to Query Design View.

2. Right click the LengthOfService field and from the popup menu select .
The Expression Builder window opens and shows the current expression.

The left column shows the objects and functions you might want to use in an expression. The middle column shows the choices or parts the the item selected in the left column. The right column shows the choices for what you selected in the middle column.

3. Experiment by choosing various items in the first column and middle column. Do you see how these columns are working??

4. Delete everything in the expression at the top of the dialog except the field name, LengthOfService:

5. Double-click Functions in the left column to expand the list.

6. Click on Built-in Functions. A list appears in the middle window of the categories of functions that Access already knows.

7. Click on Date/Time in the middle column. A list of functions appears in the right column.

8. 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!

9. Click the Help button. TaDa! A complete explanation of the function appears.

Visual Basic: Don't get too confused. This Help info is actually from the Help articles for Visual Basic, which is a programming language. The example link opens a Visual Basic example. You can include most functions in an expression in a calculated field or control.

10. Close the Help window.

11. 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.

12. Edit the expression to read:
LengthOfService: DateDiff ("yyyy", [DateHired], Now())
where the interval is "yyyy", date1 is [DateHired], and date2 is Now().

13. Click on OK to accept the new expression and close the Expression Builder window.

14. 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.

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 at least somewhat different than those in the illustration since Now() is a different date for you.)

### Correcting for Where We Are in the Year

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.

1. Switch back to Query Design View.

2. Edit the LengthOfService field to read:
LengthOfService: DateDiff("yyyy",[DateHired],Now())+Int(Format(Now(),"mmdd")<Format([DateHired],"mmdd"))

3. 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 March 7, 2006.)

4.   Save the query. (QStaff-LengthOfService)

World Travel Inc. holds a special party for employees when they reach 10 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.

1. Switch back to Query Design View.

2. Create a new calculated field:
This expression says to add 10 years to DateHired.

3. Sort the new field Ascending and remove the sorting for the three name fields.

4. Run the query.
You can see from the column 10Year that some anniversaries have already passed... unless it is January 1, 2, 3, or 4 when you do this exercise!

5. From the menu select |  and name the query  QStaff-10Year.

### Group by Anniversary and Count

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.

1. Switch back to the Query Design View.

2. Delete all the columns except EmployeeID and 10Year.

3. Click the Totals button   to show the Total row in the grid.

4. In the Total row, select Count for the EmployeeID column and Group By for the 10Year column.
Be sure that both columns have the Show box checked.

5. Run the query.
Hmmm. This grouping is on the whole date, not just the year. There actually were two employees who were hired on the same date. This is not quite what we wanted to see.

6. Save the query as QStaff-10Year-Grouped.

### Use the DatePart Function

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)

1. Switch back to Query Design View.

2. Edit the expression for the 10Year field to read: