Calculating Times Correctly

Guest · Jan 15, 2004

Next problem...I've got 4 fields labeled as follows

TRAVELSTART
TRAVELEND
TRAVELSTART
TRAVELEND

Now, I've got my calculation expression so it will somewhat correctly add my total travel times on my form. The problem is when I jump past midnight on my times it obviously adds the times incorrectly because it doesn't know that the time past midnight is a new day. Example is as follows

TRAVELSTART1 is 18:00 (1/13/04
TRAVELEND1 is 21:00 (1/13/04
TRAVELSTART2 is 21:00 (1/13/04
TRAVELEND2 is 00:30 or 30 minutes past midnight (1/14/04

When my calculation is made on my form it gives me a TOTALTRAVEL of 17:30 or 17 hours 30 minutes. Not exactly accurate. Is there any way around this? And is there any way to convert my TOTALTRAVEL to a decimal representation of the time (ie. 19:30 to 19.5, 19:15 to 19.25, etc.)? Thanks in advance.

Van T. Dinh · Jan 15, 2004

Since you didn't post your expression, we don't know
what's wrong.

However, from your description, it seems that your store
only the Time components and NOT the Date components. In
this case, your expression probably has problems in
handling subtractions that involves mid-night.

See The Access Web article for possible solution:

<http://www.mvps.org/access/datetime/date0008.htm>

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Next problem...I've got 4 fields labeled as follows:

TRAVELSTART1
TRAVELEND1
TRAVELSTART2
TRAVELEND2

Now, I've got my calculation expression so it will

somewhat correctly add my total travel times on my form.
The problem is when I jump past midnight on my times it
obviously adds the times incorrectly because it doesn't
know that the time past midnight is a new day. Example is
as follows:

TRAVELSTART1 is 18:00 (1/13/04)
TRAVELEND1 is 21:00 (1/13/04)
TRAVELSTART2 is 21:00 (1/13/04)
TRAVELEND2 is 00:30 or 30 minutes past midnight (1/14/04)

When my calculation is made on my form it gives me a

TOTALTRAVEL of 17:30 or 17 hours 30 minutes. Not exactly
accurate. Is there any way around this? And is there any
way to convert my TOTALTRAVEL to a decimal representation
of the time (ie. 19:30 to 19.5, 19:15 to 19.25, etc.)?
Thanks in advance.

Guest · Jan 18, 2004

Sorry about not including my expression. Good news is I actually figured it out for the most part using the link you provided Van. Thanks. Now what I haven't figured out is how to convert these times to a decimal representation (ie. 12:15 to 12.25 hours). All my times are based on quarters of every hour. I only record 15, 30 and 45 minutes past the hour or the hour itself (ie. 1200, 1215, 1230, 1245, 1300, etc.) so I figure it should be relatively simple to convert to decimal but I'm just not sure how. Anyway my code for adding my times are as follows:

Format([TRAVEL START 1] - 1 - [TRAVEL END 1], "Short Time")
Where TRAVEL START 1 and TRAVEL END 1 are entered in a Short Time format
and
Format([TRAVEL START 2] - 1 - [TRAVEL END 2], "Short Time")
Where TRAVEL START 2 and TRAVEL END 2 are entered in a Short Time format

What I am trying to achieve is in the following example:

TRAVEL START 1 = 08:00
TRAVEL END 1 = 09:15
TRAVEL START 2 = 10:00
TRAVEL END 2 = 11:00

These times are added and results sent to a control called TOTAL TRAVEL as 2:15. What I would like is for the TOTAL TRAVEL to be converted from the 2:15 to 2.25. If there is any way to convert these to decimal I would greatly appreciate the help. Thanks.

Van T. Dinh · Jan 18, 2004

If your [Total Travel] is DateTime (which I am not sure as you used Format
function and Format function returns String) then you can simply multiply it
by 24.

For example:

? #2:15# * 24
2.25

? #3:45# * 24
3.75

--
HTH
Van T. Dinh
MVP (Access)

JohnT said:
Sorry about not including my expression. Good news is I actually figured

it out for the most part using the link you provided Van. Thanks. Now what
I haven't figured out is how to convert these times to a decimal
representation (ie. 12:15 to 12.25 hours). All my times are based on
quarters of every hour. I only record 15, 30 and 45 minutes past the hour
or the hour itself (ie. 1200, 1215, 1230, 1245, 1300, etc.) so I figure it
should be relatively simple to convert to decimal but I'm just not sure how.
Anyway my code for adding my times are as follows:

Format([TRAVEL START 1] - 1 - [TRAVEL END 1], "Short Time")
Where TRAVEL START 1 and TRAVEL END 1 are entered in a Short Time format
and
Format([TRAVEL START 2] - 1 - [TRAVEL END 2], "Short Time")
Where TRAVEL START 2 and TRAVEL END 2 are entered in a Short Time format

What I am trying to achieve is in the following example:

TRAVEL START 1 = 08:00
TRAVEL END 1 = 09:15
TRAVEL START 2 = 10:00
TRAVEL END 2 = 11:00

These times are added and results sent to a control called TOTAL TRAVEL as

2:15. What I would like is for the TOTAL TRAVEL to be converted from the
2:15 to 2.25. If there is any way to convert these to decimal I would
greatly appreciate the help. Thanks.

Guest · Jan 21, 2004

Well, you were right. I get a "TYPE MISMATCH" error when I try to enter the times. Okay, I guess I need to go back a step here first. As I am sure you know by now, I have 4 fields for user input and 1 that updates from those 4 fields:

TRAVELSTART1 = user input field (SHORT TIME FORMAT)
TRAVELEND1 = user input field (SHORT TIME FORMAT)
TRAVELSTART2 = user input field (SHORT TIME FORMAT)
TRAVELEND2 = user input field (SHORT TIME FORMAT)

TOTAL TRAVEL = total of above 4 inputs (SHORT TIME FORMAT)

Using the FORMAT function worked great in the TOTAL TRAVEL field for adding my travel times, since I sometimes go beyond midnight and the FORMAT function seemed to work wonderfully with my times after midnight, as far as giving me the correct TOTAL TRAVEL time. Without using the FORMAT function I'm not sure how I can maintain the correct calculations in my TOTAL TRAVEL if my times run beyond midnight. However, I can't figure out how I can maintain the midnight issue and still get the times converted to decimal, since the FORMAT function returns a string. I'm sure there has to be a way to convert that string back to a datetime format but I've tried several methods with no success. I guess to make things easier to understand is I need to be able to calculate in my TOTAL TRAVEL field past midnight and at the same time, convert the TOTAL TRAVEL field to a decimal format. I hope this of some help and not more confusion. Thanks for all the great solutions.

Van T. Dinh · Jan 21, 2004

Post the ControlSource you use for the TextBox [Total Travel].

BTW, have you tested the case where [Total Travel] is greater than 24 hours?

--
HTH
Van T. Dinh
MVP (Access)

JohnT said:
Well, you were right. I get a "TYPE MISMATCH" error when I try to enter

the times. Okay, I guess I need to go back a step here first. As I am sure
you know by now, I have 4 fields for user input and 1 that updates from
those 4 fields:

TRAVELSTART1 = user input field (SHORT TIME FORMAT)
TRAVELEND1 = user input field (SHORT TIME FORMAT)
TRAVELSTART2 = user input field (SHORT TIME FORMAT)
TRAVELEND2 = user input field (SHORT TIME FORMAT)

TOTAL TRAVEL = total of above 4 inputs (SHORT TIME FORMAT)

Using the FORMAT function worked great in the TOTAL TRAVEL field for

adding my travel times, since I sometimes go beyond midnight and the FORMAT
function seemed to work wonderfully with my times after midnight, as far as
giving me the correct TOTAL TRAVEL time. Without using the FORMAT function
I'm not sure how I can maintain the correct calculations in my TOTAL TRAVEL
if my times run beyond midnight. However, I can't figure out how I can
maintain the midnight issue and still get the times converted to decimal,
since the FORMAT function returns a string. I'm sure there has to be a way
to convert that string back to a datetime format but I've tried several
methods with no success. I guess to make things easier to understand is I
need to be able to calculate in my TOTAL TRAVEL field past midnight and at
the same time, convert the TOTAL TRAVEL field to a decimal format. I hope
this of some help and not more confusion. Thanks for all the great
solutions.

John Vinson · Jan 21, 2004

Well, you were right. I get a "TYPE MISMATCH" error when I try to enter the times. Okay, I guess I need to go back a step here first. As I am sure you know by now, I have 4 fields for user input and 1 that updates from those 4 fields:

TRAVELSTART1 = user input field (SHORT TIME FORMAT)
TRAVELEND1 = user input field (SHORT TIME FORMAT)
TRAVELSTART2 = user input field (SHORT TIME FORMAT)
TRAVELEND2 = user input field (SHORT TIME FORMAT)

TOTAL TRAVEL = total of above 4 inputs (SHORT TIME FORMAT)

Using the FORMAT function worked great in the TOTAL TRAVEL field for adding my travel times, since I sometimes go beyond midnight and the FORMAT function seemed to work wonderfully with my times after midnight, as far as giving me the correct TOTAL TRAVEL time. Without using the FORMAT function I'm not sure how I can maintain the correct calculations in my TOTAL TRAVEL if my times run beyond midnight. However, I can't figure out how I can maintain the midnight issue and still get the times converted to decimal, since the FORMAT function returns a string. I'm sure there has to be a way to convert that string back to a datetime format but I've tried several methods with no success. I guess to make things easier to understand is I need to be able to calculate in my TOTAL TRAVEL field past midnight and at the same time, convert the TOTAL TRAVEL field to a decimal format. I hope this of some help and not more confusion. Thanks for all the great solutions.

Stop. You're going about this in the wrong way.

An Access Date/Time value is NOT A TEXT STRING. The format is applied
when it's ready to display. A Date/Time is stored as a double float
number, a count of days and fractions of a day (times) since midnight,
December 30, 1899. For instance, a value of #06:00 PM# is actually
stored as 0.75; and this really is equivalent to #12/30/1899 18:00:00#
on that long-ago pre-computer day.

If you will have cases where the travel starts before midnight and
ends after midnight, you would really do best to store the date and
time in the same field. To get a total of fractional hours, you can
use the Access DateDiff function to calculate the time in minutes, and
then divide by 60:

(DateDiff("n", [TravelStart1], [TravelEnd1]) +
DateDiff([TravelStart2], [TravelEnd2])) / 60.

Finally, if your table contains all four of these fields, *it is not
properly normalized*. I don't know what your table represents in the
real world, but I have to suspect that a given "trip" (or whatever it
is) might sometimes consist of one or of three travel legs; you may
want to consider having a one to many relationship with a "legs" table
with only one start and end time. This would let you use DateDiff to
calculate the duration of each leg, and a Totals query to sum them for
the whole trip.

Guest · Jan 22, 2004

Van
My control source for my TOTAL TRAVEL is referenced to my table. I have used a VB code to do the calculations and put them in the TOTAL TRAVEL field. The code is as follows

Private Sub TRAVEL_END_2_LostFocus(
Dim Msg, Style, Title, Response, MyCalc1 As Double, MyCalc2 As Doubl
Msg = "Is This The Last Call For The Day" ' Define message
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons
Title = "Last Call Invoice" ' Define title
' Display message
Response = MsgBox(Msg, Style, Title
If Response = vbYes Then ' User chose Yes
MyCalc1 = Me![TRAVEL END 1] - Me![TRAVEL START 1] ' Perform some action
MyCalc2 = Me![TRAVEL END 2] - Me![TRAVEL START 2
Me![TOTAL TRAVEL] = (MyCalc1 + MyCalc2) * 2
Else ' User chose No
If Response = vbNo The
Me![TOTAL TRAVEL] = ([TRAVEL END 1] - [TRAVEL START 1]) * 24 ' Perform some action

End I
End I
End Su

I have done this so a selection can be made as to whether this is the last invoice for the day. By the way, the calculations here do convert my TOTAL TRAVEL field to a decimal representation. All I lack now is being able to go beyond midnight. I know this is probably not the easiest way to do the calculations but I couldn't get anything else to work right. Hope this helps. Anything else you might need, just let me know and I'll post what I can. Thanks.

Van T. Dinh · Jan 22, 2004

It sounds like you store calculated value in your Table which is not
recommended by the Relational Database Design Principles.

Are [TRAVEL START 1], [TRAVEL END 1], [TRAVEL START 2], [TRAVEL END 2]
DateTime Fields in your Table as well???

--
HTH
Van T. Dinh
MVP (Access)

JohnT said:
Van,
My control source for my TOTAL TRAVEL is referenced to my table. I have

used a VB code to do the calculations and put them in the TOTAL TRAVEL
field. The code is as follows:

Private Sub TRAVEL_END_2_LostFocus()
Dim Msg, Style, Title, Response, MyCalc1 As Double, MyCalc2 As Double
Msg = "Is This The Last Call For The Day" ' Define message.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = "Last Call Invoice" ' Define title.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyCalc1 = Me![TRAVEL END 1] - Me![TRAVEL START 1] ' Perform some action.
MyCalc2 = Me![TRAVEL END 2] - Me![TRAVEL START 2]
Me![TOTAL TRAVEL] = (MyCalc1 + MyCalc2) * 24
Else ' User chose No.
If Response = vbNo Then
Me![TOTAL TRAVEL] = ([TRAVEL END 1] - [TRAVEL START 1]) * 24 ' Perform some action.

End If
End If
End Sub

I have done this so a selection can be made as to whether this is the last

invoice for the day. By the way, the calculations here do convert my TOTAL
TRAVEL field to a decimal representation. All I lack now is being able to
go beyond midnight. I know this is probably not the easiest way to do the
calculations but I couldn't get anything else to work right. Hope this
helps. Anything else you might need, just let me know and I'll post what I
can. Thanks.

Guest · Jan 23, 2004

Yes, those fields are date/time fields in my table as well.

Van T. Dinh · Jan 23, 2004

You should NOT store calculated values [TOTAL VALUE] in the Table which
violates the Relational Database Design Principles. Simply re-evaluate the
calculated values when required.

Perhaps, you should check out the Relational Database Design Theory and make
sure your Database Structure is correct.

Calculating Times Correctly

Guest

Van T. Dinh

Guest

Van T. Dinh

Guest

Van T. Dinh

John Vinson

Guest

Van T. Dinh

Guest

Van T. Dinh