Access 2000

S

Sam

I have a field for start time and a feild for finish
time, when I do a query and add a field total which is
finish time - start time I do not get the correct result.
Any help from anyone out there please. Many Thanks
 
S

Stephen Knapp

If your "time" fields in their table are marked as "date/time" fields,
your subtraction should work because times are recorded as decimal seconds
since midnight. However, if you defined your "times" as numbers (or worse,
text), then the subtraction will go really south and the results will be
unpredictable. Start by checking the field definitions. If they are
date/time fields, set up a couple of text boxes on a form and set the
ControlSource for these boxes to the time fields, BUT, format the text boxes
as general numbers. That way, you'll be able to see the "seconds" of time
for each time field. If they look bad, the problem is in how you're defining
the time fields or something else is mucking up the works. Are you doing any
thing else with these fields? Any other math? Trying to move other data
into the fields?

Steve in Ohio
 
J

John Vinson

I have a field for start time and a feild for finish
time, when I do a query and add a field total which is
finish time - start time I do not get the correct result.
Any help from anyone out there please. Many Thanks
Click to expand...

What's the datatype of the fields?

Take a look at the DateDiff() function. If you want the number of
minutes between start and end time, set the Control Source of a
textbox to

=DateDiff("n", [Start Time], [Finish Time])

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
S

Sam

Many Thanks John it works so long that both "start"
and "finish" is on the same day. There is a problem
when "start" is on 1 day and "finish" is on the next day.
Any solution please. I also wanted it to be hours so i
got it to work by divide the results by -60.

Much appreciated

Sam
-----Original Message-----
I have a field for start time and a feild for finish
time, when I do a query and add a field total which is
finish time - start time I do not get the correct result.
Any help from anyone out there please. Many Thanks
Click to expand...

What's the datatype of the fields?

Take a look at the DateDiff() function. If you want the number of
minutes between start and end time, set the Control Source of a
textbox to

=DateDiff("n", [Start Time], [Finish Time])

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.
Click to expand...
 
J

John Vinson

Many Thanks John it works so long that both "start"
and "finish" is on the same day. There is a problem
when "start" is on 1 day and "finish" is on the next day.
Any solution please. I also wanted it to be hours so i
got it to work by divide the results by -60.
Click to expand...

An Access Date/Time value is stored as a Double Float count of days
and fractions of a day (times) since midnight, Decmeber 30, 1899.
Therefore a time value of 6:00pm is actually stored as 0.75, and
corresponds to 6pm on December 30, 1899.

Therefore, if you have *pure time* values, 11pm is 22 hours later than
1 am; because Access considers it to be 1am on 12/30/1899, and 11pm on
12/30/1899.

You may want to use both the date and time in this field by using
Now() rather than Date() to fill it. This will make #11/12/2004
11:00pm# in fact two hours earlier than #11/13/2004 01:00am#; and
DateDiff will work as you would expect.

You can use "h" instead of "n" to get the time in integer hours. If
you need hours and fractions of an hour, you can subtract the times
and format the result using "hh:nn" as a format, or use expressions on
the minutes such as

DateDiff("n", [Start Time], [Finish Time]) \ 60 & format(DateDiff("n",
[Start Time], [Finish Time]) MOD 60, ":00")

If you want to keep the times all on December 30, 1899, and risk the
ambiguity (if the start time is 10am and the end time 11am, is that an
easy hour, or a marathon 25 hours? How can you tell?), you'll need
some IIF statements to add 24 hours if the difference is negative:

DateDiff("n", [Start Time], [Finish Time]) + IIF([Finish Time] >
[Start Time], 0, 1440)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top