Printable View
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.
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?
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.Quote:
Originally Posted by Novice1
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
Thank you
Don't hesitate to follow up as necessary. These are not a simple concepts.