time issuse

[kevcar40]

hi
i have two fields that are formatted as short time
start of fault
end of fault
the user enter the time the machine went into fault and the time the machine started again
i have turned thses values into numbers and suntracted the values giving me the amount of time the machine is on stop.
however when i do this for night shift i am getting the wrong answer
example
earlies Start end Result
i enter 0600 1400 8:00
Lates
i enter 1400 2200 8:00
Nights
i enter 2200 0600 16:00

How do i get the nights to report correctly


thanks


kevin

 

[Access Developer]

Use date and time. Time, even 24-hour time, is not sufficient information.
(Just for the record, Access has no "time" field or variable... it only has
a Date/Time field to represent a point in time. It also has a whole set of
time manipulation built-in functions for Date/Time fields. If you use a
Date/Time field (and set the date, too), DateDiff will probably be the
function you want to use.

Larry Linson
Microsoft Office Access MVP

 

[John W. Vinson]

hi
i have two fields that are formatted as short time
start of fault
end of fault
the user enter the time the machine went into fault and the time the machine started again
i have turned thses values into numbers and suntracted the values giving me the amount of time the machine is on stop.
however when i do this for night shift i am getting the wrong answer
example
earlies Start end Result
i enter 0600 1400 8:00
Lates
i enter 1400 2200 8:00
Nights
i enter 2200 0600 16:00

How do i get the nights to report correctly


thanks


kevin

The format is irrelevant. It doesn't change what's stored, just the display.

If you convert a time #22:00# - 10 pm - to a number 2200, then yes, the number
2200 is bigger than the number 600. It's giving THE RIGHT ANSWER, because
you're telling Access to subtract the number 2200 from the number 600; Access
has no way to know that *YOU* mean these numbers to represent times!

An Access Date/Time value - regardless of how it's formatted - is stored as a
Double Float number, a count of days and fractions of a day (times) since
midnight, December 30, 1899. As such, 06:00 is actually stored as 0.25, and is
equivalent to #12/30/1899 06:00:00#.

I would suggest storing the date and time in your fault and restart time
fields. For example, if the machine faults tonight at 10pm and is brought back
online at 5 am, you would store #12/11/2011 22:00:00# and #12/12/2011
05:00:00# respectively. You can calculate how many minutes it was down using

DateDiff("n", [TimeDown], [TimeUp])

You can format this as hh:nn with an expression

DateDiff("n", [TimeDown], [TimeUp])\60 & ":" & Format(DateDiff("n",
[TimeDown], [TimeUp]) MOD 60, "00")

This will work even for downtimes exceeding 24 hours, which you won't be able
to represent if you just store a Time value - did it come up at 5am on Monday
the 12th, or on Tuesday the 13th? You can't tell if you just store 05:00!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[kevcar40]

thank you very much for your help and time

kevin