calculate total time in report footer 1

[traycee]

Hi All! )
I am storing [StartTime] and [EndTime] in medium time format. For the report I am doing I need to find the difference between them and I really want this to show up not as time. For example....I can take [EndTime]-[StartTime] and show the time difference in hour format. Such as 1:30 p.m. end time minus 11:15 p.m. start time = 2:15, but I really need it to show up as either 2.15 or 2.25 to designate 2 1/4 hours, but when I try to do this I get a data type mismatch. Is this doable?

 

[scriverb]

Give this equation a try:

Format(DateDiff("s", [BeginTime], [EndTime]) / 3600, "0.00")

This will display the time in a decimal representation of hours.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[traycee]

Thanks....that works great! ) One more question, if you would. Is there a simple way to span midnight. If someone gets called out on a crisis say at 11 p.m. and finishes at 1 a.m. I get -22 instead of 2 hours.

 

[scriverb]

Store you time as Now() rather than the function Time(). This will preserve the date and time for both the Beginning and Ending Times. Then the equation will work as provided. You see Date/Time fields store both in the same field. The integer portion to the left of decimal is the date and the decimal portion is the time. If you designate the beginning time as the Time() function then you will store only the time.

Hopefully this will help you with understanding date/time fields.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[traycee]

I understood your description of how time is stored, but I don't know what you mean by storing my [StartTime] as Now() instead of Time(). In the table I have it stored in medium time format and I'm guessing you don't mean there. Could you explain to me where to designate the Now()? Ya know, know matter how many classes you attend or books you read their is no better teacher than experience, which I definitely need more of.

 

[scriverb]

When the records of your table were created either the user typed in a beginning time or the form control used a function call to store the systems current time in the field. There are a number of functions that could have been used: Time() or Now(). If the user just enters 3:00 pm and that's is it then there is no date value stored in the field. The same would be true if through code or the Default value the Time() function was used. The same holds true for the EndDate field.

How is your system receiving the Time for the Beginning and Ending field in the form? This is the key. There are ways to enter this data through command buttons and such where you user doesn't need to type anything in and you will get the accurate data that is needed. Let me know if you need assistance.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[traycee]

The user enters the dates in the form as 3:00 P.M. I'm using the Medium Time format. So...that means that I am just storing the time and not the time and the date. Correct? Are you saying that in order to correctly calculate the time from say 11:00 p.m. to 1:00 a.m. that in this case it would be better if I store the data as time/date and would I do that at the table level then?
I've never heard of command buttons to enter times before...that sounds interesting. I can't tell you how much I appreciate your time and assistance.

 

[scriverb]

If you create a command button and then copy and paste the following VBA code into the OnClick event procedure this will auto load the current date and Time into the control for the user. No need for any typing. Setup the BeginDate control as Locked and No TabStop. You can use Medium Time as it is okay to display it as time only but behind the control we also have the date. This would be the key:

Me.[BeginTime] = Now()

You can also make another button for the EndDate control:

Me.[EndTime]= Now()

Now we have the two fields that will automatically calculate the correct number of hours between the two with the expression that I provided.

Post back with more questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[traycee]

I loved the idea and it actually works fine, the problem though is that the times for [starttime] and [endtime] are entered after the actual event takes place...sometimes by a day and sometimes longer. For instance, maybe there is a crisis tonight and a caseworker has to go out on call. It might be a couple days before we see the paperwork for this call.

 

[scriverb]

Okay, I understand. In that case setup the input format to require date and time entry for both fields.

99/99/0000\ 09:00\ >LL;0;_

This will require them to put in the date and time for both. It is a little more work but definately required to keep the accurate data which is what this sounds like a necessity.

By the way, these aren't protective services home calls are they. You see I worked in the Michigan Family Independence Agency for quite some time developing just these type of systems. Some of the terminology that you are using sounds familiar.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[traycee]

The light bulb goes on! I do truly understand what you are saying. The instances of crisis calls that will spill over from one day to the next crossing midnight is seldom. For those few times, I could use Now() and actually input the date along with the start time and end time so that the calculation will work. A little awkward, but doable. What I didn't want to have to resort to was just having the workers give us the span of time instead of the exact time since occasionally they don't figure their own hours correctly. By using exact times, I could let the computer figure out their hours worked.

 

[scriverb]

As an afterthought here, there used to be a thread that JimmyTheGreek had posted a link to a Calendar and Time selection control that could be used just for this purpose. I can't seem to find it but had downloaded the demonstration databases for both Access 97 and Access 2k. If you would like them I can send them to you and this would afford you the ability to install these controls for picking the date from a calendar and the time also.

Just email me(see my profile) and I will send them to you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[traycee]

Thanks for the code. It works great! Not protective services but Social Services in Illinois. Foster care, adoption and the like. Only if the state of Illinois doesn't pass a budget, as of July 1 ... until they come up with something, our funding dries up. No contracts = no money.

 

[scriverb]

For others here at TT the following is a link to the FAQ written by Jim Lunde(aka JimmyTheGreek) that includes a link to download some really nice access forms calendar and time picker controls:

Microsoft: Access Forms FAQ Index faq702-639

Good luck with your project.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]