Time difference calculation (1 Viewer)

[manishmeta]

Hi,

I want to add three fields in a table namely from, to and diff .
How to add them in a form so that I can enter
the time in (xx:xx) 24hr format in the "from" and "to" fields and calculate and save the
time difference in minutes to the "diff" field automatically.

PS: I am using access 2010.

Thanks,
Manish

 

[tfurnivall]

Hi Manish

Starting point is how Access manages dates and times internally. Dates are the integer part of a floating point number, and Times are the fractional part. So a time is represented internally as a fraction between 0 and 1. It's actually the fractional part of 24 hours that the time represents, so midnight is 0, and mid-day is 0.5

So how do we reconcile that with the use of a 24 hour clock for a time? We use the TimeValue function:

TimeValue Function



This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010. Returns a Variant (Date) containing the time.
Syntax
TimeValue(time)
The required time argument is normally a string expression representing a time from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.), inclusive. However, time can also be any expression that represents a time in that range. If time contains Null, Null is returned.



Here is a snippet of code showing how you might use this function:

Function GetTime() As Single

Dim TimeString As String
Dim MyTime As Date

TimeString = "18:25"
MyTime = TimeValue(TimeString)

GetTime = MyTime

End Function


Sub test()
Debug.Print GetTime

End Sub

You can change the value in GetTime, just to see how various times return different values.

I hope that, given that pointer, your form will be running in no (Get)Time at all!

Tony

PS You'll obviously need to check that the StartTime is earlier (in the day) than the end time, and if not, add 1 to it. Also, your result will typically be formatted as hours and minutes - if you want to get a simple number of minutes, then you'll multiply the floating point value by 24 * 60. By 24 to go from parts of a day to a number of hours, and then by 60 to go to a number of minutes.

 

[manishmeta]

tfurnivall:

Thanks.

But, what problem actually I am facing is that

Its working fine if i enter simply in 24hr format it takes the value
correctly and also calculates the minutes correctly using the formula
=DateDiff("n",[open],[close])
but the final calculation is not showing up in the table but only in the form.
i.e when i navigate in the form using the value is saved and can be seen in the form but
when I open the table storing the open ,close and difference value.
The field difference is empty in all the records.
I want this to be stored in the table also.

Thanks

 

[tfurnivall]

Hmm

Does the expression normalized database mean anything to you?

Reason I ask is that if you have Time1 and Time2, then Time1-Time2 is only a calculation away. If you change either value, then Time1 - Time2 is still only a calculation away.

Now imagine that an Evil User manages to change either time1, or time2 or the difference that you want to have there. The fields are no longer only a calculation away - there is now no way on earth to recover, because at least one of the fields is wrong!

In short, you should never (as in NEVER ) store calculated results in a table when the components of the calculation are also stored there. It's walking on eggshell-thin ice, and is a waste of storage space and programming lines!

Tony

PS Of course you may be stuck with maintaining someone else's code. In this case, see how they get the values from the formfields for Time1 and Time2 into the database, and go thou and do likewise. But don't say I didn't warn you!

 

[manishmeta]

Thanks for your concern. But, Let me make it clear that the difference is all that matters to me more than time1 and time2 values. I need to sort out records later on by difference (ascending/descending ) and also need summation of differences etc later on to analyse the data.
Now, is there any way out to do that? Storing large chunk of data isnt an issue for me.
Altering the time values later isnt an issue either as this database it privately being used by me only.

 

[tamangspace]

 the difference is all that matters to me more than time1 and time2  values. I need to sort out records later on by difference  (ascending/descending ) and also need summation of differences etc later  on to analyse the data.
Now, is there any way out to do that? Storing large chunk of data isnt an issue for me. 
Altering the time values later isnt an issue either as this database it privately being used by me only.

If I understood your question, this may be a solution for you.
I created a simple table and a form containing a simple vba code. It may be what you are looking for. Only you need to do is click on 'Insert' Button after from and to data entered. You can then make queries as your need.
If it is helpful, please reply...
Good Luck

 

[manishmeta]

This serves the purpose.

 

[manishmeta]

tamangspace:

Once again I am facing the problem.
I used the same code that you gave but getting error on the calculation.
pls check my form , Only additional thing I did is visibility set to "No" for the text box which stores the temp calculated data. Even after setting the visibility ON the same error continues.

If you sort out the error modify it and also tell me what was wrong.

Thanks.

 

[tamangspace]

Manish,
The code you used is different what I wrote. The mistake you used is
t = dur_time.text

you can use it as:
't = dur_time.value' as well.

There is no any problems in setting visible=no. the problem you faced may be not refreshing the form. you can use the below code:

Private Sub Command27_Click()
Refresh
On Error Resume Next
Dim t As String
t = dur_time.Value
Duration = t
End Sub

Good Luck