help with time function

  • Thread starter Thread starter smiley1tooca
  • Start date Start date
S

smiley1tooca

Hi



I am new here and new to access. I have a statistics table that
contains two time fields along with many others. The time fields both
have a "short time" input mask. Now I want to run a query - and build
an expression in the field that allows me to track the length of time of
each call for each record. Doing a simple subtraction thing does not
work. How do I do this?



Thanks

Smiley1tooca
 
Subtracting one Date/Time field from another will return the difference in
days. To get the difference in seconds, multiply by 24*60*60.

Alternatively, you might calculate the difference in seconds using the
DateDiff function in an expression that looks something like:

DateDiff("s", [Start Time], [End Time])

Beware that a Date/Time field can contain a date component, even if you've
defined an input mask. For example, the input mask does not prevent this if
data is inserted or updated through a query or form. This can cause
problems when you calculate differences if the start and end times are
actually on different dates. If you really want to prevent this from
happening, define validation rules in your table for Date/Time fields that
look something like this:

Between 0 And 1

Note, however, that this will prevent you from deliberately entering start
and end times on different dates (for example, if a call spans midnight).

In any case, you may want to define a table-level validate rule something
like this:

[Start Time] <= [End Time]
 
Back
Top