Grouping by Hour (using DatePart)

[Persio Pucci]

Folks,

I am making a report, and I have a table with records that contain a date
field (dd/mm/yyyy hh:mm:ss). On my report I am trying to group these records
by hour, using the DatePart "h" function. What happened is that Access is
grouping in a strange way. Per example, it groups everything from 8:31 to
9:29 as 9:00, where I believe it should group from 9:00 to 9:59. Does
anybody know why does it behave like that, and how to change it?

Persio

 

[Douglas J. Steele]

How exactly are you using DatePart? As far as I know, your expected result
(i.e.: it should group everything from 9:00 to 9:59, not 8:31 to 9:29) is
how DatePart should work.

 

[John Vinson]

Folks,

I am making a report, and I have a table with records that contain a date
field (dd/mm/yyyy hh:mm:ss). On my report I am trying to group these records
by hour, using the DatePart "h" function. What happened is that Access is
grouping in a strange way. Per example, it groups everything from 8:31 to
9:29 as 9:00, where I believe it should group from 9:00 to 9:59. Does
anybody know why does it behave like that, and how to change it?

Persio

VERY odd. It does not do so in my hands:

?datepart("h",#11:59:59#)
11
?datepart("h",#11:00:01#)
11
?datepart("h",#12:00:00#)
12

What version of Access do you have? Service packs up to date? Could
you post the actual SQL of the query with which you're doing the
grouping?

An alternative way (should it be needed) to extract the hour is

Format([timefield], "hh")

This will get a string 00 through 23 which can also be used for
grouping.

 

[Persio Pucci]

To everybody that replied to me,

I am using Access 2000 (9.0.4402 SR-1).

This is the Control Source for this textbox:

=DatePart("h";[Data])

I am grouping it first by the field Data, on Day, Interval 1, and then I am
grouping again, by the Field Data again, on Hour, Interval 1.

This should do it fine, right? Take a look at this printscreen
http://persio.pucci.sites.uol.com.br/access.jpg. The surrounded area at the
report is the results for the marked records. The first column shows the
DatePart "h" result (08:00 for this example), the second column is the total
records for this group, and the third column is the sum of the duration
field. I uploaded this sample file
http://persio.pucci.sites.uol.com.br/test_call_records.zip with the records
of the printscreen so if you want to check it by yourself...

Another strange thing is happening. Take a look at the next 6 records after
the marked ones. Acording to this crazy logic, they should be returned at
the report as the DatePart 09:00, right? Why in heaven does Access repeats
the 8 in the next line? :/ And it happens in the next ones, randomly I
think, and I can't find a reason for that...

Going insane...

Tks everyone!