I attempting to count file uploads that occur between certain times. I have the counts when they occur between contiguous time periods but am getting odd results when we jump the midnight barrier. Below is the query I have that jumps midnight. If anyone can tell me how to correct this, I would greatly appreciate.
SELECT MonthName(Month([DNG Data].Date)) AS [Month], Year([DNG Data].Date) AS [Year], Count([DNG Data].Date) AS MonthlyCount
FROM [DNG Data]
WHERE ((([DNG Data].Time) Between "22:00:00" And "06:59:59"))
GROUP BY Year([DNG Data].Date), Month([DNG Data].Date);
I believe I have found one solution. I separated the hours till midnight and the hours after midnight with an OR. That produced the number I expected. If there is a better way however, I would love to hear it.
You can concatenate date and time information in Access to form a single time unit, such as #1/1/2010 12:45:00#. As you see, all you need to do is provide the hash marks and the single space between correctly formatted dates and times. This construct works in queries as well as in VBA.
Ergo, assuming you want to 1) look at the window between last night at 10 pm and 6 this morning, and 2) you want the time window to remain constant but the date to change every day, you can say something like this in VBA:
Dim TDate as Date, BTime AS String, ETime AS String, ParmString As String
TDate = Date() -1 (for example)
BTime = "#" & Tdate & " 22:00:00#"
ETime = "#" & DAte() & " 06:00:00#"
In your query, you can have a Parameter called [Enter Window]. Continuing with the VBA, the next line is
ParmString = "Between " & BTime & " And " & ETime & """" (That's 4 double quotes).
.Parameters("Enter Window") = ParmString
You can enhance that to compensate for weekends and holidays, etc. by using Weekday() and other functions in your VBA.