How to get the difference in hours only?

[Chris Clavius]

Am using access 2003, I have a form that has two unbound text fields which I made its format as "short time". the fields are to accept Time in(eg 22:00) and time out (eg 03:20) How can I get the difference in hours only (in here 5)? Any fast help please

Regars
Chris

[hype261]

Take a look at the DateDiff function. Here is a reference.

http://office.microsoft.com/en-us/access-help/datediff-function-HA001228811.aspx

[Chris Clavius]

I have tried date diff() function like "bTest = DateDiff("h", dteBegin, dteEnd)" but did not work a I get result as 11/12/1899

[hype261]

You are going to have to do some custom code to get the correct answer. I probably should have mentioned this before. Here is something quick I just put together so it may not work on all degenerate cases.

Private Function TimeDiff(ByRef timeOne As TextBox, ByRef timeTwo As TextBox) As Integer
On Error GoTo Err_TimeDiff
    Dim dateOne As Date
    Dim dateTwo As Date
    
    If Not IsNull(timeOne) And Not IsNull(timeTwo) Then
        dateOne = DateAdd("yyyy", 1, timeOne)
        dateTwo = DateAdd("yyyy", 1, timeTwo)
        
       TimeDiff = DateDiff("h", dateOne, dateTwo)
       
    End If

Exit_TimeDiff:
    Exit Function
    
Err_TimeDiff:
    MsgBox Err.Description
    Resume Exit_TimeDiff
End Function

[Chris Clavius]

Dim strDateTime As Date
Dim bTest As Date
Dim dteBegin As Date
Dim dteEnd As Date

If IsNull(Me.timeT aken) Or IsNull(Me.TimeP rocess) Then
MsgBox "TIME IN AND OUT MUST BE ENTERED", vbOKOnly, "SignIn/Out: Time is Missing" _

If IsNull(Me.timeT aken) Then
timeTaken.SetFo cus
ElseIf IsNull(Me.TimeP rocess) Then
TimeProcess.Set Focus
End If
Else
dteBegin = DateAdd("yyyy", 1, timeTaken)
dteEnd = DateAdd("yyyy", 1, TimeProcess)
bTest = DateDiff("h", dteBegin, dteEnd)

If bTest Then 'Check for Success If Successful, it can be used to test time range

MsgBox bTest & " diff"
End If

End If



DO I GO WRONG ANYWHERE, I STILL GET UNEXPECTED UNSWER, remember my texboxes format are "short date" with input mask [00:00;;_]

[Chris Clavius]

Dim strDateTime As Date
Dim bTest As Date
Dim dteBegin As Date
Dim dteEnd As Date

If IsNull(Me.timeT aken) Or IsNull(Me.TimeP rocess) Then
MsgBox "TIME IN AND OUT MUST BE ENTERED", vbOKOnly, "SignIn/Out: Time is Missing" _

If IsNull(Me.timeT aken) Then
timeTaken.SetFo cus
ElseIf IsNull(Me.TimeP rocess) Then
TimeProcess.Set Focus
End If
Else
dteBegin = DateAdd("yyyy", 1, timeTaken)
dteEnd = DateAdd("yyyy", 1, TimeProcess)
bTest = DateDiff("h", dteBegin, dteEnd)

If bTest Then 'Check for Success If Successful, it can be used to test time range

MsgBox bTest & " diff"
End If

End If



DO I GO WRONG ANYWHERE, I STILL GET UNEXPECTED UNSWER, remember my texboxes format are "short date" with input mask [00:00;;_]

[hype261]

You declare bTest as a Date variable. In my example I am returning an integer.

Change this...

Dim btTest as Date

to...

Dim btTest as Integer

[Chris Clavius]

That work perfectly, one more problem. It gives out -ve integers when I want difference in date between 22:00 and 02:00. Can this also be solved?

[hype261]

Chris,

I don't understand your question. Do you mean negative integers?

If so change....

bTest = DateDiff("h", dteBegin, dteEnd)

to

bTest = Abs(DateDiff("h ", dteBegin, dteEnd))

[Chris Clavius]

Thank you so much hype, You have made my day.... Am so happy to have People like you in this site, My last problem was getting time difference like -19 or -20 in times like 21:00 and 04:00, I solved it this way down.. Please post me any comment to improve it to look professional :)
Thanx in advace


If bTest Then 'Check for Success If Successful, it can be used to test time range
If bTest < 0 Then 'in-case we have -ve time difference
bTest = bTest + 24
End If
'MsgBox bTest & " diff"
If bTest <= 4 Then
tempStatus = 1
ElseIf bTest > 4 Then
tempStatus = 2
End If
txtTime.Value = tempStatus 'initializing to a bound text field txtTime
End If
End If

[hype261]

I added some comments about your code down here. In the future you should use code tags to make reading your code easier.

If bTest Then <-------What are you testing for here???? This will exclude 0 hours.
   If bTest < 0 Then      
      bTest = bTest + 24
   End If
   'MsgBox bTest & " diff"

    If bTest <= 4 Then 
         tempStatus = 1
    ElseIf bTest > 4 Then <---Isn't this calculation always going to be true?????         
         tempStatus = 2
    End If

    txtTime.Value = tempStatus 'initializing to a 
            'bound text field txtTime
    End If
End If

[Chris Clavius]

Hey,
I have omited line 1 and it works perfectly even with 0 hours. Thank you :)

Line 9 (ElseIf bTest > 4 Then <---Isn't this calculation always going to be true?????).. I dont see if it has problems, or I didnt understand what you mean

Thannx again

[hype261]

In line 7 you test ...

If bTest <= 4 then

So if this is false then bTest must be greater than 4

if Line 9 you test

ElseIf bTest > 4

What you are doing here is a redundant test for something you have already established.

You can just change line 9 to Else

[Chris Clavius]

Thans alot, I have changed it and it works.