Tables & Queries:
Calculated Values - Dates

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

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

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

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

---

Use the DateAdd Function

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.

Syntax for DateAdd: DateAdd(interval, number, date)

1. Switch back to Query Design View.
    

2. Create a new calculated field:
   10Year:DateAdd("yyyy",10,[DateHired])
   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  File |  Save As  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:
   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.
    
3. Run the query.
    Now we can see groupings!
    
    
4. Save this query. (QStaff-10Year-Grouped)
    
   To see the number of anniversaries in a particular year, you could add the year to the Criteria row in the 10Year column.