time calculation

[David]

Hello all,


I have a query with the following expression, Minutes: DateDiff("n",
[StartDateTime], [EndDateTime]). This works fine with the exception of
the start time is before midnight and the end time after midnight which
results into a negative number. Is there a way to prevent negative numbers?


Thank you,
David

 

[Joan Wild]

See How to handle midnight in calculations:
http://www.mvps.org/access/datetime/date0008.htm

 

[Douglas J. Steele]

Realistically, you shouldn't be using just time: the Date data type is
intended for a timestamp (date and time).

If you cannot (or will not) change your application, consider writing a
wrapper function for the DateDiff function that compares the times, and adds
a day to the end time if it's less than the start time.

Under the covers, date/time fields are stored as 8 byte floating point
numbers, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal part represents the time as a
fraction of a day. When you have only a time, it's stored as that time on 30
Dec, 1899. In other words, 6:00 AM is stored as 0.25, Noon is stored as 0.5,
3:00 PM is stored as 0.625 and so on. When your end time is earlier than
your start time, adding 1 to it should fix your problem.

 

[Jeff Boyce]

David

There's a "kludge" fix for that, but it suggests that there may be
underlying issues...

What are you storing in your Date/Time fields? ... the one you are using in
the DateDiff() function? They are Date/Time data type fields, right?

Are you storing ONLY times? If so, you just THINK you are storing only
times! Access is storing Date/Time values ... so one way you get negative
values is if the "end-time-after-midnight" were on an "earlier" date.

And the kludge? If you take the absolute value of DateDiff() (e.g.,
Abs(DateDiff(...)), you get a positive number.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

I don't think so, Jeff.

?DateDiff("n", #23:30:00#, #01:15:00#)
-1335

1335 is 22 hours 15 minutes, not the 1 hour 45 minutes I'd expect.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

David

There's a "kludge" fix for that, but it suggests that there may be
underlying issues...

What are you storing in your Date/Time fields? ... the one you are using
in the DateDiff() function? They are Date/Time data type fields, right?

Are you storing ONLY times? If so, you just THINK you are storing only
times! Access is storing Date/Time values ... so one way you get negative
values is if the "end-time-after-midnight" were on an "earlier" date.

And the kludge? If you take the absolute value of DateDiff() (e.g.,
Abs(DateDiff(...)), you get a positive number.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello all,


I have a query with the following expression, Minutes: DateDiff("n",
[StartDateTime], [EndDateTime]). This works fine with the exception of
the start time is before midnight and the end time after midnight which
results into a negative number. Is there a way to prevent negative
numbers?


Thank you,
David

 

[Jeff Boyce]

I misunderstood the OP. I thought the post said that the difference was
correct, but negative...

My bad!

Jeff Boyce
Microsoft Office/Access MVP

I don't think so, Jeff.

?DateDiff("n", #23:30:00#, #01:15:00#)
-1335

1335 is 22 hours 15 minutes, not the 1 hour 45 minutes I'd expect.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

David

There's a "kludge" fix for that, but it suggests that there may be
underlying issues...

What are you storing in your Date/Time fields? ... the one you are using
in the DateDiff() function? They are Date/Time data type fields, right?

Are you storing ONLY times? If so, you just THINK you are storing only
times! Access is storing Date/Time values ... so one way you get
negative values is if the "end-time-after-midnight" were on an "earlier"
date.

And the kludge? If you take the absolute value of DateDiff() (e.g.,
Abs(DateDiff(...)), you get a positive number.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello all,


I have a query with the following expression, Minutes: DateDiff("n",
[StartDateTime], [EndDateTime]). This works fine with the exception of
the start time is before midnight and the end time after midnight which
results into a negative number. Is there a way to prevent negative
numbers?


Thank you,
David

 

[David]

Realistically, you shouldn't be using just time: the Date data type is
intended for a timestamp (date and time).

If you cannot (or will not) change your application, consider writing a
wrapper function for the DateDiff function that compares the times, and adds
a day to the end time if it's less than the start time.

Under the covers, date/time fields are stored as 8 byte floating point
numbers, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal part represents the time as a
fraction of a day. When you have only a time, it's stored as that time on 30
Dec, 1899. In other words, 6:00 AM is stored as 0.25, Noon is stored as 0.5,
3:00 PM is stored as 0.625 and so on. When your end time is earlier than
your start time, adding 1 to it should fix your problem.

Doug and Jeff I appreciate your help with this unfortunately this form
is going to be filled out after midnight and some of the data will
pertain to before midnight is why I don't use a timestamp and am using
just time. Doug I understand what you are saying by if the end time is
earlier than the start time add a 1 but I don't understand how to
write it in my query.

Thank you in advance for your help,
David

 

[Douglas J. Steele]

Doug and Jeff I appreciate your help with this unfortunately this form is
going to be filled out after midnight and some of the data will pertain to
before midnight is why I don't use a timestamp and am using just time.
Doug I understand what you are saying by if the end time is earlier than
the start time add a 1 but I don't understand how to write it in my
query.

I don't see what difference it makes that some of the data will pertain to
before midnight and some after.

Today's March 3rd. Even if you were still dealing with last night's data,
what's preventing you from saying 11:30 PM on March 2nd, 2007, rather than
simply saying 11:30 PM?

However, if you can't do it, you can't. Did you check the reference Joan
gave http://www.mvps.org/access/datetime/date0008.htm ?

What I was suggesting is rather than

Minutes: DateDiff("n", [StartDateTime], [EndDateTime])

you could try

Minutes: DateDiff("n", [StartDateTime], [EndDateTime] + IIf([StartDateTime]

[EndDateTime], 1, 0))

or

Minutes: IIf([StartDateTime] > [End DateTime], DateDiff("n",
[StartDateTime], [EndDateTime] + 1, DateDiff("n", [StartDateTime],
[EndDateTime])