Consulting

Results 1 to 5 of 5

Thread: Solved: Counting occurances of Time

  1. #1
    VBAX Newbie
    Joined
    Jun 2004
    Location
    Georgia, USA
    Posts
    2
    Location

    Solved: Counting occurances of Time

    I have a field call TimeIn (Long Time format). I'm trying to count the number of occurances per hour.

    I created a report. In the summary of the report I placed the following code: =If [TimeIn] Between #08:00:00# And #08:59:59# Then Count [TimeIn]

    The code is wrong but I'm not sure how it's wrong. Any help would be appreciated.

  2. #2
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Firstly, you haven't specified a date within the time.

    Secondly are you using code (what you have is some strange amalgam of SQL and VBA) or are you putting this into the ControlSource of a textbox?

  3. #3
    VBAX Regular GP George's Avatar
    Joined
    Jul 2004
    Location
    Puget Sound, WA
    Posts
    26
    Location

    Count Function

    Quote Originally Posted by Novice1
    I have a field call TimeIn (Long Time format). I'm trying to count the number of occurances per hour.

    I created a report. In the summary of the report I placed the following code: =If [TimeIn] Between #08:00:00# And #08:59:59# Then Count [TimeIn]

    The code is wrong but I'm not sure how it's wrong. Any help would be appreciated.
    I suggest you add the function to count the number of instances per hour in the query which provides the recordsource for your report. This is true because you need to know the count, I assume, for each hour, not just a single hour and a query is the most efficient way to calculate that.

    Add a field to the query by dragging the [TimeIn] field to the query grid. Then
    change it to the following syntax:

    Hours: DatePart("h", [TimeIn])

    This will return a field in your query with only the "hour" portion of the [TimeIn] field. In other words, both "8:15AM" and "8:25AM" will be returned as "8"

    Now, add a second field to the query with the following syntax:

    InstancesPerHours: DatePart("h", [TimeIn])

    You will use the "Totals" function in the query to generate the Count value. Find the icon on your Query Design menu which looks like the Sigma Symbol: Σ

    that will reveal the Totals row in the query grid. For the field called "Hours" leave it as "Group By", but under the InstancesPerHour field, change it to "Count".

    Post back if you have further questions.

    George


    "We're all in this together."
    -Red Green

  4. #4
    VBAX Newbie
    Joined
    Jun 2004
    Location
    Georgia, USA
    Posts
    2
    Location
    Thank you

  5. #5
    VBAX Regular GP George's Avatar
    Joined
    Jul 2004
    Location
    Puget Sound, WA
    Posts
    26
    Location
    Don't hesitate to follow up as necessary. These are not a simple concepts.
    "We're all in this together."
    -Red Green

Posting Permissions

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