Representing Time

[JamieNC]

I'm trying to create a field that represents time in days, hours, minutes and seconds in such a way that, if the number of hours goes beyond 24, a unit will be added to the day section. Eg:

00:22:15 - 0 days, 22 hours and 15 minutes
16:04:13 - 16 days, 4 hours and 13 minutes

ie a quantity of time as opposed to a particular time of the day.

I've tried messing about with date formatting, but it doesn't seem to be able to handle representing time as anything other than the time of day (adding 3 minutes on to 23:58 gives a total of 00:01). Is there any sort of workaround that will allow me to utilise this formatting to represent dates or, indeed, any other alternatives which might work as a solution?

Many thanks,

Jamie

 

[cghoga]

Hi Jamie.

try this...

it depends on what your exact need is, but you could use this...

in a table...

create a field called Date (whatever you want to call it)
and toward the bottom under the General tab set its Format to:

dd:hh:nn:ss

in the Default Value enter

= Now()

when you create a new record it should time stamp the record with this format.

OR

on a form...

create an unbound text box.
in the control source of the text box insert = Now().
in the format insert dd:hh:nn:ss.
run the form.

in this case, if the day goes beyond 24 hrs it will automatically roll over.

is this what you're looking for?

i hope this helps you.

 

[psemianonymous]

You can use the DateDiff() function to calculate how many days difference between any two values. For you this means that you can calculate "rollover" hours via:

DateDiff("d",0,[yourTimeField])


PS--setting the "formatting" of a date field will NOT affect the actual data stored. It will only affect how you see the data. What I'm saying is that even though you only saw 0:01 because of your formatting, the data actually stored 1 day, 0:01.

This is all assuming that you store the value using the Date/Time data type, which I hope you did.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[FancyPrairie]

I'm not sure on how you plan on using your numbers, but you could create a long integer and store your "time" there. The number would represent the number of minutes since "midnight". Where "midnight" is a fixed starting point in time.

For example,

if (lngTime = 1) then it would read 0 days 0 hours 1 minute.

if (lngTime = 2881) then it would read 2 days 0 hours 1 minute.

To convert lngTime to days, hours, minutes your calculation would look like what's in the following function. Copy and paste the function in a new module and run it, by passing various numbers to check it out. For example if you pass it the number 2999, you results would be 2 days 1 hour 59 minutes. Note that it returns a string. However, the calculations are there if you want to do something other than returning a string.

MsgBox TestTime(2999)

Function TestTime(lngTime As Long) As String

    Dim lngDay As Long
    Dim lngMin As Long
    Dim lngHr As Long
    
    lngDay = lngTime \ 1440
    lngHr = (lngTime - (lngDay * 1440)) \ 60
    lngMin = ((lngTime - lngDay * 1440) - (lngHr * 60))
    
    TestTime = lngDay & " days " & lngHr & " hrs " & lngMin & " mins"

End Function