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:
  1. Dim cnn As New ADODB.Connection

  2. Dim rst As New ADODB.Recordset

  3. Dim timeAry(0 To 47) As Variant

  4. Dim k, e As Integer

  5. Dim strSql As String

  6.  
  7. k = 3

  8.  
  9. e = 0

  10.  
  11. ' Setup the Array

  12. timeAry(0) = "12:00 AM"

  13. timeAry(1) = "12:30 AM"

  14. timeAry(2) = "1:00 AM"

  15. timeAry(3) = "1:30 AM"

  16. timeAry(4) = "2:00 AM"

  17. timeAry(5) = "2:30 AM"

  18. timeAry(6) = "3:00 AM"

  19. timeAry(7) = "3:30 AM"

  20. timeAry(8) = "4:00 AM"

  21. timeAry(9) = "4:30 AM"

  22. timeAry(10) = "5:00 AM"

  23. timeAry(11) = "5:30 AM"

  24. timeAry(12) = "6:00 AM"

  25. timeAry(13) = "6:30 AM"

  26. timeAry(14) = "7:00 AM"

  27. timeAry(15) = "7:30 AM"

  28. timeAry(16) = "8:00 AM"

  29. timeAry(17) = "8:30 AM"

  30. timeAry(18) = "9:00 AM"

  31. timeAry(19) = "9:30 AM"

  32. timeAry(20) = "10:00 AM"

  33. timeAry(21) = "10:30 AM"

  34. timeAry(22) = "11:00 AM"

  35. timeAry(23) = "11:30 AM"

  36. timeAry(24) = "12:00 PM"

  37. timeAry(25) = "12:30 PM"

  38. timeAry(26) = "1:00 PM"

  39. timeAry(27) = "1:30 PM"

  40. timeAry(28) = "2:00 PM"

  41. timeAry(29) = "2:30 PM"

  42. timeAry(30) = "3:00 PM"

  43. timeAry(31) = "3:30 PM"

  44. timeAry(32) = "4:00 PM"

  45. timeAry(33) = "4:30 PM"

  46. timeAry(34) = "5:00 PM"

  47. timeAry(35) = "5:30 PM"

  48. timeAry(36) = "6:00 PM"

  49. timeAry(37) = "6:30 PM"

  50. timeAry(38) = "7:00 PM"

  51. timeAry(39) = "7:30 PM"

  52. timeAry(40) = "8:00 PM"

  53. timeAry(41) = "8:30 PM"

  54. timeAry(42) = "9:00 PM"

  55. timeAry(43) = "9:30 PM"

  56. timeAry(44) = "10:00 PM"

  57. timeAry(45) = "10:30 PM"

  58. timeAry(46) = "11:00 PM"

  59. timeAry(47) = "11:30 PM"

  60. ' End Array

  61.  
  62. Do Until e = 48

  63.  
  64. rst.Open strSql, cnn, adOpenStatic

  65.  
  66. Range("A" & k).Value = ""

  67.  
  68. Range("A" & k).Value = timeAry(e)

  69.  
  70. Range("B" & k).Value = 0

  71.  
  72. Do Until rst.EOF

  73.  
  74. If (timeAry(e) >= rst.Fields!Sun_Start And timeAry(e) <= rst.Fields!Sun_End) Then Range("B" & k).Value = Range("B" & k).Value + 1

  75.  
  76. rst.MoveNext

  77.  
  78. Loop

  79.  
  80. rst.Close

  81.  
  82. k = k + 1

  83.  
  84. e = e + 1

  85.  
  86. Loop

  87.  
  88. cnn.Close

  89.  
  90. Set rst = Nothing

  91. 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?