PDA

View Full Version : Solved: Counting occurances of Time



Novice1
06-08-2004, 05:11 AM
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.

SJ McAbney
06-08-2004, 06:04 AM
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?

GP George
07-15-2004, 12:38 PM
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

Novice1
07-15-2004, 12:47 PM
Thank you

GP George
07-15-2004, 01:07 PM
Don't hesitate to follow up as necessary. These are not a simple concepts.