calculating time

[Mary]

Hello,

I have a table that contains dates and times of downtime for factory
machines. I need to create a query/report that calculates how many minutes
a machine was down during a certain time period.

I have four fields in my table; downdate, downtime, update and finally
uptime... The data is stored as Date/Time.

So, as an example; let's say I have one machine that went down last night
11/21(downdate) at 11:55 pm (downtime) and came back up at 11/22 (update) at
12:00 am (uptime).

How do I go about creation a query that can calculate this for me? I would
like the result to be in minutes.

Many thanks,

Mary

 

[Douglas J. Steele]

Realistically, you should only have 2 fields: downdatetime and updatetime.
The Date/Time data type is intended to represent a timestamp: under the
covers, it's an 8 byte floating point number where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day. If you have a
need for only the date, or only the time, you can use the DateValue (or
TimeValue) function to extract it from the timestamp.

You can use the DateDiff function to calculate the difference between the
two:

DateDiff("n", [downdatetime], [updatetime])

(n is for minutes)

If you absolutely cannot combine the fields in your table, you can simply
add them together:

DateDiff("n", [downdate] + [downtime], [update] + [uptime])