Specific time durations for tasks

[srburk]

I have a table with forty different records. Each record has the name of a task and an assumed duration of time that will pass for each task.

Currently, my duration of time is listed in each record in the a text format and is displayed like the following examples.

:10
:15
:20
:30
:45
:90
:120
:360

translation:
:10 is ten minutes
:15 is fifteen minutes
:20 is 20 minutes
:30 is 30 minutes
:45 is 45 minutes
:90 is 1 hour and 30 minutes
:120 is 2 hours
:360 is 6 hours

I'd like to be able to format the minutes in the original table in such a way that I could display the times as __ hours, __ minutes in reports and queries after doing calculations.

Would appreciate any help!!!

 

[The_Doc_Man]

Your problem is that time fields aren't what you think they are. So when you try to do something rational with them, you get funky dates and such.

The best way to do this is to leave the minutes alone in the table that shows the estimated times for each task. Do NOT try to record the times as date/time fields. Record the actual minutes (as INTEGER or LONG minutes) somewhere. Write yourself a VBA function to return a text string in the desired format given an INTEGER/LONG input of the number of minutes.

You might get away with something this simple:

public function mins_to_hm( mins as long ) as string

dim mpart as long
dim hpart as long

mpart = mins mod 60
hpart = mins / 60

mins_to_hm = format$( hpart, "####" ) & ":" & format$( mpart, "00" )

end function

Put that in a general module. (otherwise it cannot be public even though it says it is.)

Then, when you have to convert your minutes to conventional hours, call that function to return a displayable string.

NOTE: look up Format$ and "User-Defined Numeric Formats" in Access Help if you are not sure what this does.

Now, if you are doing this as the difference between two date/time fields, you need to know that the difference in that case is a floating-point number. BUT you can convert it using function DateDiff to get the minutes between the two times. I'll leave that as an exercise in Access Help for you to try.