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
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
-
13 3170
I have tried date diff() function like "bTest = DateDiff("h", dteBegin, dteEnd)" but did not work a I get result as 11/12/1899
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
-
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;;_]
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;;_]
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
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?
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))
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
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
-
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
In line 7 you test ...
So if this is false then bTest must be greater than 4
if Line 9 you test
What you are doing here is a redundant test for something you have already established.
You can just change line 9 to Else
Thans alot, I have changed it and it works.
Sign in to post your reply or Sign up for a free account.
Similar topics
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#,...
|
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...
|
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...
|
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
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |