Adding Time

[MK10]

I've done a thorough search of this site looking for an example of how to add lieral time. I've seen examples of DateAdd and DateDiff but neither do what I want. I want to add time together.

I am using Access 2000. For an example I would like the ability to add:
1hours 20 mins
2hours 20 mins
5hours 20 mins

Anyone can see this adds up to 9 hours, why is there not a function or format that will allow me to do this, or am I missing something.

Please help it's driving me bananas.

Thanks

Barbara

 

[TonyJollans]

Hi Barbara,

You can just add times together. If they are in string variables you'll need to convert them to times first. For example ...

MsgBox TimeValue("1:20") + TimeValue("2:40")

will give "04:00:00" (hh:mm:ss is the default format - use the Format function if you want something else) and ...

Dim t1 As Date, t2 As Date
t1 = "1:20": t2 = "2:30"
MsgBox t1 + t2

will give "03:50:00"

BUT Beware if the total is more than 24 hours because the data type is in fact date and time and you will get some odd looking results. Experiment with different values and you'll see what I mean.

There have been several discussions on this topic in these fora and the reason that you found dateadd and datediff is that they can be used for times, and they might be (at least part of) what you want.

Enjoy,
Tony

 

[MK10]

Hi TonyJollans,

Sorry for the delay in responding, been on holiday.

The 24 hours bit is what I've been trying to overcome. Thanks for trying anyway.

B

 

[scriverb]

How exactly are your Hours and Minutes stored in your table? Seperately or together? If together, in what format? Is the format the string representation that you provided in your example? I hope not. What is the table name and field names of the storage fields also?

Post back with this information and I can provide you with code to make this work for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MK10]

Hi scriverb,

My Hours and Minutes were stored as Short Time, but this didn't work and are now Integer which still doesn't work. In the table 1 hour looks like 100 but on screen look like 1:00 The table is called tblRecharges and the field is called RechargeTime. Thanks.

Barbara

 

[CajunCenturion]

Can you not use the DateAdd function? This function works quite well with times as well as dates.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TonyJollans]

Hi All,

Dates and Times cause no end of problems.

It is possible to store them in all sorts of ways, but most people (including most questioners at Tek-Tips) store them as Date/Time types and get confused by the difference between the way the dates (and times) are stored and the ways they are formatted on input and output. An example of this is Barbara's statement that Hours and Minutes were stored as Short Time.

For the record, Date/Time variables are stored as a number of days (e.g. 0.5 is half a day or 12 hours). Access (and the other Office applications, although they differ slightly) uses some default formatting on output which suits casual use, and allows for a variety of formatting by Users for more serious use. It also applies an amount of intelligence to interpreting input and provides a couple of conversion functions, again for more serious use.

When converted on input or output, positive values are treated as being offset from midnight (at the start of) 30 Dec 1899 (I have no idea why). Negative values can be used but there is an added complication and for current discussion purposes it is assumed that all values are positive.

Having got a basic description out of the way, let's look at times. Times can be added and subtracted at will and the result will always be correct as a number of days. There is, however, no provision anywhere for formatting Date/Time variables as durations rather than absolute times on absolute dates. This gives rise to two (related) problems when working with times which may be greater than 24 hours; the first (which is relatively easy to address) is getting output formatted as desired; the second (which is harder) is getting input both valid and in a format which will be understood by Access.

If all you want to do is add up individual times, each less than 24 hours, and report the total, which may be more than 24 hours then define everything as Date/Time and add them up (use DateAdd if you wish); then on output use this:

Format(

TimeTotal

,"hh") + 24*Int(Cdbl(

TimeTotal

)) & Format(

TimeTotal

,":nn:ss")

If you want more, Bill's solution may do what you want, I'm not sure yet.

Bill - I have downloaded your d/b and started looking at it. I will get back to you later.

Enjoy,
Tony

 

[MK10]

Hi Tony, As I said, the 24 hours was the problem. Once again thanks.

Barbara

 

[judgehopkins]

For more info, look at these:

http://msdn.microsoft.com/library/d...vbadev/html/chapter2workingwithdatestimes.asp

and

http://www.developershandbook.com/downloads/2162ch03.pdf

"I have never killed a man, but I have read many obituaries with great pleasure." - Clarence Darrow