Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Queries> Calculated Dates
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: Queries: Calculated Dates

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.


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!

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

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

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)
    "m" is already being used for the month.
  • "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 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

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


Icon: Step-by-Step 

Step-by-Step: Calculated Values - Dates

 Icon: Step-by-Step

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:  Class disk, Projects database from previous lesson open.

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

Simple Calculation: 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 (projects2-Lastname-Firstname.accdb ) from your Class disk.

  2. In the Navigation Pane, right click on the query QStaff-FullName.
  3. Select  Copy.

  4. Right click in a blank area of the Navigation Pane and select  Paste .
    A dialog appears for you to use to name the copied query.

  5. Name the new query QStaff-LengthOfService

  6. Query Design View: QStaff-LengthOfServiceIcon: Design View Open the new query in Query Design View.

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

  8. In the next blank column, create a new field:
    LengthOfService: Now()-[DateHired]

    This expression subtracts the date/time hired from the current date/time.

  9. TAB out of the field.

  10. Column is too narrow to show the values.Icon: Run Run the query.
    Icon: Datasheet View The column LengthOfService shows hash marks, ######## because the column is too narrow to show the value.

  11. Double click on the right edge of the column heading to widen the column.

    Query Datasheet View: QStaff-LengthOfService, with decimal number values

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

  12. Button: Save (Access 2010) 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 during the day!

Syntax for Format: Format (datetime, pattern)

  1. Icon: Design View Switch back to Query Design View.

  2. 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")
     
    Zoom window: 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.

    TipUse the Zoom window so you can see what you are doing. Increase the font size for the Zoom window if you wish.
     

  3. Icon: Run Run the query.
    Icon: Datasheet View Better!!

    Query Datasheet View: QStaff-LengthOfService- using Format function

    These values look like years.

    Remember, your values in the LengthOfService column will be different from the illustration!


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. Icon: Design View Switch back to Query Design View.

  2. Expression Builder: LengthOfService: Format(Now(),"yyyy")-Format([DateHired],"yyyy")Right Click Menu: Build 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 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.

  3. Icon: Experiment 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. Expression Builder: DateDiff inserted with placeholdersDouble-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. Help: DateDiff function Click the definition in the bottom of the window.

    Icon: Access 2007 Access 2007: Click the Help button.

    TaDa! A VERY complete explanation of the function appears. You must be online for Access 20913 and 2016.
     
    TipVisual 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.

  10. Close the Help window.
  11. Expression Builder: DateDiff inserted with placeholders

    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())
    Expression Builder: DateDiff function completed
    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. Query Datasheet View: QLengthOfService with DateDiffIcon: Run Run the query.
    Icon: Datasheet View 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.)


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.

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.

  1. Icon: Design View Switch back to Query Design View.  

  2. Edit the LengthOfService field to add a new part at the end:
    LengthOfService: DateDiff("yyyy",[DateHired],Now())+Int(Format(Now(),"mmdd")<Format([DateHired],"mmdd"))
    Zoom: 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!

  3. Icon: Run Run the query.
    Icon: Datasheet View 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.)


  4. Button: Save (Access 2010) Save the query but do not close it yet.
    [QStaff-LengthOfService]
     

Use the DateAdd Function

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)

  1. Icon: Design View Switch back to Query Design View.

  2. Create a new calculated field:
    10Year:DateAdd("yyyy",10,[DateHired])

    Zoom window for field 10YearThis expression says to add 10 years to DateHired.

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

  4. Icon: Run Run the query.
    Icon: Datasheet View 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!

    Query Datasheet: 10Year (Access 2016)

    The illustration was captured from Access 2016, April 30, 2016.

  5. Icon: Design View Switch back to Query Design View.

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

  7. Icon: Run Run the query.

    Query including 15- and 20-year anniversary dates (Access 2016)

    Icon: Datasheet View The datasheet shows the two new columns.

  8. Button: Save (Access 2010) Save Object As QStaff-Anniversaries.

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. Icon: Design View Switch back to the Query Design View for QStaff-Anniversaries.
  2. Query Design View: 10Year - countingDelete all the columns from QStaff-10Year except EmployeeID and 10Year.
  3. Click the Totals button Button: Totals  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. Query Datasheet View: 10Year - countingIcon: Run Run the query.
    Icon: Datasheet View 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!  

  6. Button: Save (Access 2010) Save Object 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. Icon: Design View Switch back to Query Design View.
  2. Zoom: 10Year: DatePart("yyyy",DateAdd("yyyy",10,[DateHired]))

    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. Query Datasheet View: QStaff-10Year, successfully grouped on yearIcon: Run Run the query.
    Icon: Datasheet View Now we can see groupings!
  4. Button: Save (Access 2010) Save.
    [QStaff-10Year-Grouped]
     
    TipTo see the number of anniversaries in a particular year, you could add the year to the Criteria row in the 10Year column.
  5. Similarly, create queries QStaff-15Year-Grouped and QStaff-20Year-Grouped.
  6. Close all open objects.