Calculation Time

[Faction21]

I'm having difficulty figuring out how to calculate time. To test this i have three fields. The first field is StartTime and the second field is EndTime. The third field, TotalTime stores a calcualtion, which is how much time is between the start and end times, which is basically the total. The format is medium date, and the data entry is through a form.

I have tried subtracting the contents of the fields making a text box bound to total, and tried using addition but all it gives me is a totaly unrelated amont of time.

I would prefere to use VBA, but if not, is it possible to set a form to calculate this? Or a query / table to do so?



-Thanks in advance.

 

[trucktime]

Time

You may want to check this link:

http://www.access-programmers.co.uk/forums/showthread.php?t=62570

 

[Faction21]

Thanks, but i tried the download, and when i load it it comes up with "cannot find date library" errors and such. That data base is not working for me, and I have A2k. I tried to look at some of the stuff, but it is beyond me how it works, im pretty new at VbA and I am at a begginer level.

Another thing I should mention is tha tthese times that are being entered, are never going to be over 24 hours.
For Example:

StartTime:9:00am EndTime: 2:30pm TotalTime: 5.5 Hours

 

[The_Doc_Man]

Your problem in a nutshell is the internal format used by Access to store date fields.

Dates are a DOUBLE (yes, 64-bit floating) number of DAYS (and fractions thereof) since the system reference date, which for Windows is usually taken as 1/1/1900, at midnight, which for Windows is the first time of day that could have been expressed having that date. (By Windows standards, 11:59:59 PM is the last second of a given date. 0:00:00 AM is the first second of a given date.)

OK, when you take time differences, if you do it directly, you get the number of days difference between the two dates. All well and good - except that the resulting number cannot be correctly interpreted. It LOOKS like a date and time in January of 1900. Access cannot tell that it is now a RELATIVE time - there is no intrinsic format for such a thing. A date is a date to Access, and that is the end of that story. So if the times are less than 24 hours apart, that date looks like something on 1/1/1900. Trying to interpret that date in relative rather than absolute terms becomes horrifically difficult if you don't understand it.

So that is why you want to use something like DATEDIFF function to take the difference between two times and specify the units of your difference. Like, difference in minutes, then divide by 60 (as a REAL or DOUBLE) to get the hours and fractions of hours involved. The DATEDIFF function will correctly handle date anomalies and will return the difference in whatever units make sense to your required resolution.

As to using VBA, DATEDIFF (as a library function) is available both in VBA and in SQL queries. So no problem there.

 

[Faction21]

So if I put

DateDiff("???",[StartTime],[EndTime])

what do i put to get the time? also, what data type would i use for the total time field? It would be nice for it to be double, and go out to 2 decimal places.

 

[Pat Hartman]

The useful dates database sample includes a function you can use. If you are having trouble with the db it is because the necessary libraries are in a different place on your PC than they are on the PC where I developed the sample. Open any code module and go to Tools/References. If there is a missing reverence, you can try deleting it. If that doesn't resolve the problem, you'll actually need to locate and load the missing reference file.

 

[Faction21]

What type of references would I need? And how would I know if it is missing or not?

 

[Pat Hartman]

What type of references would I need? And how would I know if it is missing or not?

If you had followed my directions you would know whether any reference was marked as missing.

This is a problem with Access, not a problem with the sample database. Databases created in one environment will frequently fail when run in a different environment because various necessary reference libraries are located in different directories.

 

[DLB]

im pretty new at VbA and I am at a begginer level.

Maybe you will find Jon K's sample here helpful:
http://www.access-programmers.co.uk/forums/showthread.php?t=62733


~

... the data entry is through a form.

I have tried subtracting the contents of the fields making a text box bound to total, and tried using addition but all it gives me is a totaly unrelated amont of time.

In fact, it did give you a related amount of time in a double precision number. All you needed was to format it properly.

See the Medium Time format of the "txtTotalTime" unbound text box on the form in the database attached. The database was created in Access 2003 but in Access 2000 file format.

 

[Faction21]

OMG wow, this is much simpler than i thought! you guys are awsome! thanks soo much for your time to help me out.

Now all I need to do is get it to store that calculated time in a new field in the table. How can I assign the calulated time in the textbox to the new field in the table1 called TotalTime?

 

[DLB]

Bind the TotalTime text box to the TotalTime field i.e. change these two properties:

Name : TotalTime
Control Source : TotalTime


Then put this line of code in the After Update event of the EndTime text box:

Private Sub EndTime_AfterUpdate()
   
   Me.TotalTime = Me.EndTime - Me.StartTime
   
End Sub

 

[Pat Hartman]

You should not be storing calculated values. Especially something as simple as a subtraction. Calculate the value in a query as needed.

BTW the default unit in date/time arithmetic is day. If you want the result in any other unit, you MUST use the DateDiff() function.