Functions for Calculating and for Displaying Date/Time Values in Access

Functions for Calculating and for Displaying Date/Time Values in Access from Microsoft covers a whole bunch of data and time topics.

It has functions for everything from the last day of the current quarter to the first day of the current week. It also has some functions for working with intervals, although you'll see they differ slightly from my advice on the right way to store intervals.

Read more »

How do I let my user pick a date from a calendar?

There are tons of ways to do this- some use ActiveX controls, some do not. Some have more functionality than others.
For a simple approach, the DateTimePicker ActiveX control is probably your best bet. The only disadvantage is that it's an ActiveX control, which can be difficult to manage if you have to install in a variety of environments. Using the DateTimePicker is really just like using a combo box. Use it bound or unbound. Just like a combo box, when the user clicks the arrow the control shows the list of choices- in this case a calendar where the user can click on a date.

Some Microsoft links:

• A walkthough of using the Access Calendar Control
• A reference for the DateTimePicker ActiveX control
• A reference for the ActiveX MonthView control

Some non-Microsoft web links:

• A nice round-up on UtterAccess.com of several good solutions
• A Pop-up Calendar for your Access Forms (Tutorial using the Access Calendar Control)

Calculating elapsed time is more than just numbers

Here's a neat post on the Access Team Blog. It's a function you can use in your application to display elapsed time in a more "friendly" way. The units used and the precision applied are determined by the magnitude of the interval- take a look!

Quote:
I didn’t just want to show the hours or even days elapsed, but something more in sync with the way I want the information given to me- specifically that when dates are closer to the current time they are represented more precisely and dates that are farther away are shown generically.

***Update 2009-04-04: This article has also been posted on MSDN as: Constructing Modern Time Elapsed Strings in Access 2007.

Grouping records in a query by 15 minute intervals

I recently posted on Grouping records on date/time values in an Access report. But what if you want to group on time in a query? Here's an expression you could use in a query for grouping records into fifteen minute intervals. It uses DatePart() to get the minutes part of the time and then uses Int() and multiplication to cut off to the nearest 15 minutes. Then it uses DatePart again to get the hours part and sticks them together.

MyInterval: Format(DatePart("h",[MyTime]),"00") & ":" & Format(Int(DatePart("n",[MyTime])/15)*15,"00")

Grouping records on date/time values in an Access report

Access reports have some really powerful capabilities. This article demonstrates using the sorting and grouping functions to group reports on a range of dates.

reports, Date and time, grouping, group by month,group by date

Summing Times, or Date/Time Data: An Instant or a Duration?

The question on the forum goes something like this:

I'm trying to add these times together but when it gets over 24 hours it goes back to zero instead of going to 25. What am I doing wrong?

The Access date/time data type is well suited to storing an instant in time. A date and/or time when something happened. It's not well suited to storing durations- how long something took. For that you're better to work with a number. It could be a number of seconds, minutes, hours or days: whatever's meaningful in your situation. And you'll have to decide what field size is appropriate as well. My message is: intervals or durations should be treated as numbers, not times.

Here are some example queries. First our sample data- start and stop times for productions runs on systems:

SystemID   RunStartTime   RunStopTime
========   ============   ===========
   1        6:00:00 AM     2:00:00 PM
   1        3:00:00 PM    11:00:00 PM
   2        4:00:00 AM     6:00:00 AM
   2        8:00:00 AM    11:00:00 AM
   2        1:00:00 PM     2:00:00 PM
   2        3:00:00 PM    11:00:00 PM

Here's where many people start with something like this- they subtract the times:

SELECT
  SystemID,
  RunStartTime,
  RunStopTime,
  [RunStopTime]-[RunStartTime] AS RunLength
FROM tblRunTimes;

If you tell Access to format the RunLength as time it works pretty well. The times for each run work OK.

With our sample data it even works to sum the times per system:

SELECT
  SystemID,
  Sum([RunStopTime]-[RunStartTime]) AS TotalRunLength
FROM tblRunTimes
GROUP BY SystemID;

But it falls apart when you sum the times for all the systems:

SELECT
  Sum([RunStopTime]-[RunStartTime]) AS TotalRunLength
FROM tblRunTimes;

gives you the following result:

TotalRunLength
  6:00:00

What happened? The total of all the run times is actually 30 hours. But Access thinks it's a date and time. So it's actually one day later at 6:00 in the morning.

The answer is to use DatePart() to calculate each run length in seconds:

SELECT
  SystemID,
  RunStartTime,
  RunStopTime,
  DateDiff("s",[RunStartTime],[RunStopTime]) AS RunLength
FROM tblRunTimes;

then when you sum it you get total run time in seconds per system:

SELECT
  SystemID,
  Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength
FROM tblRunTimes
GROUP BY tblRunTimes.SystemID;

or for all systems:

SELECT
  Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength
FROM tblRunTimes;

Treating the data as seconds will make your calculations work properly. You can sum, average, do stats, whatever you need. When you present information to the users you'll have to decide whether they want to see seconds or something else. To show hours instead simply divide by 3,600 (the number ofseconds in an hour). Here's an all SQL approach to break the total seconds down into days, hours, minutes and seconds:

SELECT
  Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength,
  Int([RunLength]/(60*60*24)) AS Days,
  Int(([RunLength]-([Days]*(60*60*24)))/(60*60)) AS Hours,
  Int(([RunLength]-[Days]*(60*60*24)-[Hours]*(60*60))/(60)) AS Minutes,
  Int([RunLength]-[Days]*(24*60*60)-[Hours]*(60*60)-[Minutes]*(60)) AS Seconds
FROM tblRunTimes;

See also: How to store, calculate, and compare Date/Time data in Microsoft Access , More Date and Time Topics on MyMSAccessBlog.

Formatting Cardinal Dates

Here's a neat one on cardinal dates. Cardinal numbers, you'll remember from school, are the "st's and th's", for example 1st and 2nd and 103rd. This article includes a function for 1st January 2008 that could easily e cumstomized for a variations like January 1st 2008.

Using dates and times in Access

This article from Microsoft Office Online is a good round-up of date and time topics.

Read more »