Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010

    Unanswered: Counting question


    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);

    Thanks again.
    Scott Medaugh

  2. #2
    Join Date
    Aug 2010

    A workable solution found.

    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.

  3. #3
    Join Date
    May 2004
    New York State
    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).
        With CurrentDb.QueryDefs("QueryNameInQuotes")
            .Parameters("Enter Window") = ParmString
        End With
    You can enhance that to compensate for weekends and holidays, etc. by using Weekday() and other functions in your VBA.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts