adding and multiplicating time

  • Thread starter Thread starter W
  • Start date Start date
W

W

Hi all,

I have a query which takes some data from a table in which I have a
time-field : a predefined duration for certain operations. The property for
this field is “long time†(hh:mm:ss).

However, when I want to do calculations on this field, e.g. I have 5 times
operation x that takes 1:00:00 and 10 times operation y that takes 00:45:00,
I can’t neither multiplicate this field nor add the results.

Does anyone have a clue ?

Thank you for your answer,

W
 
W, you will run into a raft of problems if you use a Date/Time field to
store durations like that.

It is possible to multiply and sum them, but it would be much better to use
a Number field that stores the value as a whole number of seconds. You can
then calculate and display it as hours : minutes : seconds. You can even use
an unbound text box to let the user enter it that way, and use its
AfterUpdate event procedure calculate the number of seconds to store.
 
The problem stems from that fact that DateTime fields are meant to store a
point in time. You want to store duration of time. This means you should be
storing a number that indicates how many minutes (or seconds if you need finer
granularity) an operation takes.

Once you do that then calculations such as you wish to do are much simpler.
You will need to use some math if you wish to display total duration in the
format of hours, minutes, and seconds.

You can try the following
DateDiff("n",0,[OperationTime]) * 5
that should return a number. BUT be careful that the OperationTime field does
not contain a date. If it does you will get strange results. If you suspect
that the field contains a date then you can use the TimeValue function to
strip out the time only.
DateDiff("n",0,TimeValue([OperationTime])) * 5

If that returns 300 and you want to see that as 5 hours and no minutes, you
can use the following expression where X would be replaced by your expression
to calculate the duration of minutes.

X \ 60 & ":" & Format(X Mod 60,"00") & ":00"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
John, thank you. I jyst read Allen's answer and I understand I have to
review my data.

Thanks at any rate for your kindness,

W

John Spencer said:
The problem stems from that fact that DateTime fields are meant to store a
point in time. You want to store duration of time. This means you should be
storing a number that indicates how many minutes (or seconds if you need finer
granularity) an operation takes.

Once you do that then calculations such as you wish to do are much simpler.
You will need to use some math if you wish to display total duration in the
format of hours, minutes, and seconds.

You can try the following
DateDiff("n",0,[OperationTime]) * 5
that should return a number. BUT be careful that the OperationTime field does
not contain a date. If it does you will get strange results. If you suspect
that the field contains a date then you can use the TimeValue function to
strip out the time only.
DateDiff("n",0,TimeValue([OperationTime])) * 5

If that returns 300 and you want to see that as 5 hours and no minutes, you
can use the following expression where X would be replaced by your expression
to calculate the duration of minutes.

X \ 60 & ":" & Format(X Mod 60,"00") & ":00"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi all,

I have a query which takes some data from a table in which I have a
time-field : a predefined duration for certain operations. The property for
this field is “long time†(hh:mm:ss).

However, when I want to do calculations on this field, e.g. I have 5 times
operation x that takes 1:00:00 and 10 times operation y that takes 00:45:00,
I can’t neither multiplicate this field nor add the results.

Does anyone have a clue ?

Thank you for your answer,

W
Click to expand...
Click to expand...
 
Back
Top