473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to get the difference in hours only?

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
Dec 6 '10 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. Private Function TimeDiff(ByRef timeOne As TextBox, ByRef timeTwo As TextBox) As Integer
  2. On Error GoTo Err_TimeDiff
  3.     Dim dateOne As Date
  4.     Dim dateTwo As Date
  5.  
  6.     If Not IsNull(timeOne) And Not IsNull(timeTwo) Then
  7.         dateOne = DateAdd("yyyy", 1, timeOne)
  8.         dateTwo = DateAdd("yyyy", 1, timeTwo)
  9.  
  10.        TimeDiff = DateDiff("h", dateOne, dateTwo)
  11.  
  12.     End If
  13.  
  14. Exit_TimeDiff:
  15.     Exit Function
  16.  
  17. Err_TimeDiff:
  18.     MsgBox Err.Description
  19.     Resume Exit_TimeDiff
  20. End Function
  21.  

13 3170
hype261
207 100+
Take a look at the DateDiff function. Here is a reference.

http://office.microsoft.com/en-us/ac...001228811.aspx
Dec 6 '10 #2
I have tried date diff() function like "bTest = DateDiff("h", dteBegin, dteEnd)" but did not work a I get result as 11/12/1899
Dec 6 '10 #3
hype261
207 100+
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.

Expand|Select|Wrap|Line Numbers
  1. Private Function TimeDiff(ByRef timeOne As TextBox, ByRef timeTwo As TextBox) As Integer
  2. On Error GoTo Err_TimeDiff
  3.     Dim dateOne As Date
  4.     Dim dateTwo As Date
  5.  
  6.     If Not IsNull(timeOne) And Not IsNull(timeTwo) Then
  7.         dateOne = DateAdd("yyyy", 1, timeOne)
  8.         dateTwo = DateAdd("yyyy", 1, timeTwo)
  9.  
  10.        TimeDiff = DateDiff("h", dateOne, dateTwo)
  11.  
  12.     End If
  13.  
  14. Exit_TimeDiff:
  15.     Exit Function
  16.  
  17. Err_TimeDiff:
  18.     MsgBox Err.Description
  19.     Resume Exit_TimeDiff
  20. End Function
  21.  
Dec 6 '10 #4
Dim strDateTime As Date
Dim bTest As Date
Dim dteBegin As Date
Dim dteEnd As Date

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

If IsNull(Me.timeTaken) Then
timeTaken.SetFocus
ElseIf IsNull(Me.TimeProcess) Then
TimeProcess.SetFocus
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;;_]
Dec 6 '10 #5
Dim strDateTime As Date
Dim bTest As Date
Dim dteBegin As Date
Dim dteEnd As Date

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

If IsNull(Me.timeTaken) Then
timeTaken.SetFocus
ElseIf IsNull(Me.TimeProcess) Then
TimeProcess.SetFocus
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;;_]
Dec 6 '10 #6
hype261
207 100+
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
Dec 6 '10 #7
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?
Dec 6 '10 #8
hype261
207 100+
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))
Dec 6 '10 #9
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
Dec 6 '10 #10
hype261
207 100+
I added some comments about your code down here. In the future you should use code tags to make reading your code easier.

Expand|Select|Wrap|Line Numbers
  1. If bTest Then <-------What are you testing for here???? This will exclude 0 hours.
  2.    If bTest < 0 Then      
  3.       bTest = bTest + 24
  4.    End If
  5.    'MsgBox bTest & " diff"
  6.  
  7.     If bTest <= 4 Then 
  8.          tempStatus = 1
  9.     ElseIf bTest > 4 Then <---Isn't this calculation always going to be true?????         
  10.          tempStatus = 2
  11.     End If
  12.  
  13.     txtTime.Value = tempStatus 'initializing to a 
  14.             'bound text field txtTime
  15.     End If
  16. End If 
  17.  
Dec 6 '10 #11
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
Dec 7 '10 #12
hype261
207 100+
In line 7 you test ...

Expand|Select|Wrap|Line Numbers
  1. If bTest <= 4 then
  2.  
So if this is false then bTest must be greater than 4

if Line 9 you test

Expand|Select|Wrap|Line Numbers
  1. 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
Dec 7 '10 #13
Thans alot, I have changed it and it works.
Dec 9 '10 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: John | last post by:
Hi I am trying to find difference between two time values in hours. I have tried the following? ? dateDiff("h", #23:00:00#, #23:00:00#) gives 0 as expected. ? dateDiff("h", #00:00:00#,...
1
by: Sunny | last post by:
I am interested in finding out the time it takes to complete a certain section of my source code How do i determine the time difference between the beginning of a process and its end. I need to get...
1
by: Usman Ghani | last post by:
Hi There, I want to display below page creation time and so I have two variables as: Dim start_time As New DateTime Dim end_time As New DateTime First I am storing value in start_time on page...
3
by: Richard | last post by:
Hi, Is there any way to get the time difference between Central(US) and GMT in vb.net. I'll appreciate your help/suggestion. Thanks RC
2
by: cmrhema | last post by:
Hello I have a table GPSDATA which consists of VehicleNo,Speed,CurrentDate,slno,status This data gets updated every minute.And every minute the slno(serial no) will be incremented...
2
by: idealfellow | last post by:
I am using the following to get the time: puts Time.now --> starting of my script <MY CODE> puts Time.now --> end of my script I want to get the Time difference between two Time.now...
2
by: hridya | last post by:
can any one help me i want to ge the time difference betwwen two times eg: time1=12:10:10 time 2:=12:20:10 i want time2-time1
3
by: Steve | last post by:
I am trying to calculate elapsed travel times for flights. My plan is to enter the local departure time, the departure city and the local arrival time and city. These times would be standardised...
3
by: RN1 | last post by:
A Form has 2 TextBoxes where in users enter date & time. Example (in mm/dd/yyyy format): 09/20/2008 17:54 (1st TextBox) 09/29/2008 6:13 (2nd TextBox) How do I find out how much time (in...
2
by: Guruganesh | last post by:
i have to calculate the time diference of two file names namely File 1: CBE03KMD_2010_03_08_23_06_36.upl File 2: CBE03KMD_2010_03_08_22_11_47.upl see the date is same but the time is also...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.