[RESOLVED] Check If Time Value is between Two Times
Never had to check this before and for some reason its not working the way I want it to.
Basically I am trying to see if a time from an array is between two times from a SQL query.
So
time Code:
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim timeAry(0 To 47) As Variant
Dim k, e As Integer
Dim strSql As String
k = 3
e = 0
' Setup the Array
timeAry(0) = "12:00 AM"
timeAry(1) = "12:30 AM"
timeAry(2) = "1:00 AM"
timeAry(3) = "1:30 AM"
timeAry(4) = "2:00 AM"
timeAry(5) = "2:30 AM"
timeAry(6) = "3:00 AM"
timeAry(7) = "3:30 AM"
timeAry(8) = "4:00 AM"
timeAry(9) = "4:30 AM"
timeAry(10) = "5:00 AM"
timeAry(11) = "5:30 AM"
timeAry(12) = "6:00 AM"
timeAry(13) = "6:30 AM"
timeAry(14) = "7:00 AM"
timeAry(15) = "7:30 AM"
timeAry(16) = "8:00 AM"
timeAry(17) = "8:30 AM"
timeAry(18) = "9:00 AM"
timeAry(19) = "9:30 AM"
timeAry(20) = "10:00 AM"
timeAry(21) = "10:30 AM"
timeAry(22) = "11:00 AM"
timeAry(23) = "11:30 AM"
timeAry(24) = "12:00 PM"
timeAry(25) = "12:30 PM"
timeAry(26) = "1:00 PM"
timeAry(27) = "1:30 PM"
timeAry(28) = "2:00 PM"
timeAry(29) = "2:30 PM"
timeAry(30) = "3:00 PM"
timeAry(31) = "3:30 PM"
timeAry(32) = "4:00 PM"
timeAry(33) = "4:30 PM"
timeAry(34) = "5:00 PM"
timeAry(35) = "5:30 PM"
timeAry(36) = "6:00 PM"
timeAry(37) = "6:30 PM"
timeAry(38) = "7:00 PM"
timeAry(39) = "7:30 PM"
timeAry(40) = "8:00 PM"
timeAry(41) = "8:30 PM"
timeAry(42) = "9:00 PM"
timeAry(43) = "9:30 PM"
timeAry(44) = "10:00 PM"
timeAry(45) = "10:30 PM"
timeAry(46) = "11:00 PM"
timeAry(47) = "11:30 PM"
' End Array
Do Until e = 48
rst.Open strSql, cnn, adOpenStatic
Range("A" & k).Value = ""
Range("A" & k).Value = timeAry(e)
Range("B" & k).Value = 0
Do Until rst.EOF
If (timeAry(e) >= rst.Fields!Sun_Start And timeAry(e) <= rst.Fields!Sun_End) Then Range("B" & k).Value = Range("B" & k).Value + 1
rst.MoveNext
Loop
rst.Close
k = k + 1
e = e + 1
Loop
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Returns
Code:
12:00 AM 18
12:30 AM 26
1:00 AM 26
1:30 AM 26
2:00 AM 26
2:30 AM 27
3:00 AM 27
3:30 AM 27
4:00 AM 27
4:30 AM 27
5:00 AM 27
5:30 AM 27
6:00 AM 27
6:30 AM 27
7:00 AM 27
7:30 AM 20
8:00 AM 20
8:30 AM 19
9:00 AM 18
9:30 AM 0
10:00 AM 0
10:30 AM 15
11:00 AM 17
11:30 AM 18
12:00 PM 18
12:30 PM 26
1:00 PM 26
1:30 PM 26
2:00 PM 26
2:30 PM 27
3:00 PM 27
3:30 PM 27
4:00 PM 27
4:30 PM 27
5:00 PM 27
5:30 PM 27
6:00 PM 27
6:30 PM 27
7:00 PM 27
7:30 PM 20
8:00 PM 20
8:30 PM 19
9:00 PM 18
9:30 PM 0
10:00 PM 0
10:30 PM 15
11:00 PM 17
11:30 PM 18
For some reason 9:30 and 10:30 get 0's. Anyone have any ideas?
Re: Check If Time Value is between Two Times
LOL nevermind figured it out
Code:
Sub Test()
Dim timeAry(0 To 47) As Variant
' Setup the Array
timeAry(0) = #12:00:00 AM#
timeAry(1) = #12:30:00 AM#
timeAry(2) = #1:00:00 AM#
timeAry(3) = #1:30:00 AM#
timeAry(4) = #2:00:00 AM#
timeAry(5) = #2:30:00 AM#
timeAry(6) = #3:00:00 AM#
timeAry(7) = #3:30:00 AM#
timeAry(8) = #4:00:00 AM#
timeAry(9) = #4:30:00 AM#
timeAry(10) = #5:00:00 AM#
timeAry(11) = #5:30:00 AM#
timeAry(12) = #6:00:00 AM#
timeAry(13) = #6:30:00 AM#
timeAry(14) = #7:00:00 AM#
timeAry(15) = #7:30:00 AM#
timeAry(16) = #8:00:00 AM#
timeAry(17) = #8:30:00 AM#
timeAry(18) = #9:00:00 AM#
timeAry(19) = #9:30:00 AM#
timeAry(20) = #10:00:00 AM#
timeAry(21) = #10:30:00 AM#
timeAry(22) = #11:00:00 AM#
timeAry(23) = #11:30:00 AM#
timeAry(24) = #12:00:00 PM#
timeAry(25) = #12:30:00 PM#
timeAry(26) = #1:00:00 PM#
timeAry(27) = #1:30:00 PM#
timeAry(28) = #2:00:00 PM#
timeAry(29) = #2:30:00 PM#
timeAry(30) = #3:00:00 PM#
timeAry(31) = #3:30:00 PM#
timeAry(32) = #4:00:00 PM#
timeAry(33) = #4:30:00 PM#
timeAry(34) = #5:00:00 PM#
timeAry(35) = #5:30:00 PM#
timeAry(36) = #6:00:00 PM#
timeAry(37) = #6:30:00 PM#
timeAry(38) = #7:00:00 PM#
timeAry(39) = #7:30:00 PM#
timeAry(40) = #8:00:00 PM#
timeAry(41) = #8:30:00 PM#
timeAry(42) = #9:00:00 PM#
timeAry(43) = #9:30:00 PM#
timeAry(44) = #10:00:00 PM#
timeAry(45) = #10:30:00 PM#
timeAry(46) = #11:00:00 PM#
timeAry(47) = #11:30:00 PM#
' End Array
i = 0
Do Until i = 47
If Sgn(#7:00:00 AM# - timeAry(i)) + Sgn(#11:30:00 AM# - timeAry(i)) = 0 Then Debug.Print timeAry(i)
i = i + 1
Loop
End Sub
Re: [RESOLVED] Check If Time Value is between Two Times
You should reconsider:
Code:
Sub Test()
Const TimeStart = #7:00:00 AM#
Const TimeEnd = #11:30:00 AM#
Dim timeAry(0 To 47) As Date
Dim i As Long
For i = 0 To 47
timeAry(i) = TimeSerial(0, i * 30, 0)
Next
For i = 0 To 47
If (timeAry(i) > TimeStart) And (timeAry(i) < TimeEnd) Then
Debug.Print timeAry(i)
End If
Next
End Sub
or even shorter:
Code:
Sub Test()
Const TimeStart = #7:00:00 AM#
Const TimeEnd = #11:30:00 AM#
Dim timeAry(0 To 47) As Date
Dim i As Long
For i = 0 To 47
timeAry(i) = TimeSerial(0, i * 30, 0)
If (timeAry(i) > TimeStart) And (timeAry(i) < TimeEnd) Then
Debug.Print timeAry(i)
End If
Next
End Sub
Re: [RESOLVED] Check If Time Value is between Two Times
Quote:
Originally Posted by anhn
You should reconsider:
Code:
Sub Test()
Const TimeStart = #7:00:00 AM#
Const TimeEnd = #11:30:00 AM#
Dim timeAry(0 To 47) As Date
Dim i As Long
For i = 0 To 47
timeAry(i) = TimeSerial(0, i * 30, 0)
Next
For i = 0 To 47
If (timeAry(i) > TimeStart) And (timeAry(i) < TimeEnd) Then
Debug.Print timeAry(i)
End If
Next
End Sub
or even shorter:
Code:
Sub Test()
Const TimeStart = #7:00:00 AM#
Const TimeEnd = #11:30:00 AM#
Dim timeAry(0 To 47) As Date
Dim i As Long
For i = 0 To 47
timeAry(i) = TimeSerial(0, i * 30, 0)
If (timeAry(i) > TimeStart) And (timeAry(i) < TimeEnd) Then
Debug.Print timeAry(i)
End If
Next
End Sub
Ahh nice didn't even think about using the TimeSerial function thanks anhn, and shorter more efficient code is always nice :)
Re: [RESOLVED] Check If Time Value is between Two Times
Actually it dose not work the way I need it to.
For Example if the Array is 12:00:00 AM and the StartDate is 8:00:00 PM and the EndDate is 1:00:00 AM it still returns false instead of true.
