Time Calculations past midnight

[DaveN]

I have an Access data base in which I have seperate fields for date and time.
Truth Date, Truth Time, Reported Date, Reported Time. Time is recorded in 24
hour time (hh:nn:ss). I am doing a Datediff on the time fields datediff ("s",
[truth time],[reported time]). Everything works except for the problem of
going past midnight (ex: truth time=23:59:25, reported time=00:01:25) I get a
diff of -86280 seconds. Using the built in expressions in Access is there a
way to allow for this and get the correct number of seconds?

Also I have tried building multiple expressions in the expression builder
(such as datediff then an IIf > statement) and Access cannont understand the
expression. Is there some format I am missing when trying to do this in
expression builder.

 

[John Vinson]

I have an Access data base in which I have seperate fields for date and time.
Truth Date, Truth Time, Reported Date, Reported Time. Time is recorded in 24
hour time (hh:nn:ss). I am doing a Datediff on the time fields datediff ("s",
[truth time],[reported time]). Everything works except for the problem of
going past midnight (ex: truth time=23:59:25, reported time=00:01:25) I get a
diff of -86280 seconds. Using the built in expressions in Access is there a
way to allow for this and get the correct number of seconds?

An Access Date/Time value is actually stored as a Double Float count
of days and fractions of a day since midnight, December 30, 1899. Any
pure time value is actually a time on that long-ago date: e.g.
18:00:00 is actually stored as 0.75.

In your case, 00:01:25 IS in fact a minute plus past midnight (on
December 30, 1899); 23:59:25 is in fact a few seconds before the end
of that day - nearly 24 hours later than the [Reported Time]. The
expression is calculated correctly.

I can make two suggestions:

1. Instead of using just Time values, store the date and time in the
same field. #8/17/2005 23:59:25# is in fact before #8/18/2005
00:01:25#, and DateDiff will calculate the difference correctly.

2. If (for some reason which I can't fathom) you want to maintain the
dates in the 21st century and the times in the 19th century, you can
add them:

DateDiff("s", [Truth Date] + [Truth Time], [Reported Date] + [Recorded
Time])

will work.

Also I have tried building multiple expressions in the expression builder
(such as datediff then an IIf > statement) and Access cannont understand the
expression. Is there some format I am missing when trying to do this in
expression builder.

Probably, but since I don't understand what you're building it's hard
to say. The expression builder is designed to create AN expression,
which can then be inserted into code or into a property of a control.
What are you trying to accomplish (and can it not be accomplished by
simply typing the multiple expressions rather than using the builder)?

John W. Vinson[MVP]

 

[DaveN]

Thanks John that worked. Appreciate the help

I have an Access data base in which I have seperate fields for date and time.
Truth Date, Truth Time, Reported Date, Reported Time. Time is recorded in 24
hour time (hh:nn:ss). I am doing a Datediff on the time fields datediff ("s",
[truth time],[reported time]). Everything works except for the problem of
going past midnight (ex: truth time=23:59:25, reported time=00:01:25) I get a
diff of -86280 seconds. Using the built in expressions in Access is there a
way to allow for this and get the correct number of seconds?

An Access Date/Time value is actually stored as a Double Float count
of days and fractions of a day since midnight, December 30, 1899. Any
pure time value is actually a time on that long-ago date: e.g.
18:00:00 is actually stored as 0.75.

In your case, 00:01:25 IS in fact a minute plus past midnight (on
December 30, 1899); 23:59:25 is in fact a few seconds before the end
of that day - nearly 24 hours later than the [Reported Time]. The
expression is calculated correctly.

I can make two suggestions:

1. Instead of using just Time values, store the date and time in the
same field. #8/17/2005 23:59:25# is in fact before #8/18/2005
00:01:25#, and DateDiff will calculate the difference correctly.

2. If (for some reason which I can't fathom) you want to maintain the
dates in the 21st century and the times in the 19th century, you can
add them:

DateDiff("s", [Truth Date] + [Truth Time], [Reported Date] + [Recorded
Time])

will work.

Also I have tried building multiple expressions in the expression builder
(such as datediff then an IIf > statement) and Access cannont understand the
expression. Is there some format I am missing when trying to do this in
expression builder.

Probably, but since I don't understand what you're building it's hard
to say. The expression builder is designed to create AN expression,
which can then be inserted into code or into a property of a control.
What are you trying to accomplish (and can it not be accomplished by
simply typing the multiple expressions rather than using the builder)?

John W. Vinson[MVP]