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

    Unanswered: Counting question

    Hello,

    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
    Posts
    2

    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.
    Thanks.

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    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:

    Code:
    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

    Code:
        ParmString = "Between " & BTime & " And " & ETime & """" (That's 4 double quotes).
        With CurrentDb.QueryDefs("QueryNameInQuotes")
            .Parameters("Enter Window") = ParmString
            .Execute
        End With
    You can enhance that to compensate for weekends and holidays, etc. by using Weekday() and other functions in your VBA.

    SL

Posting Permissions

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