Grouping by Hour (using DatePart)

  • Thread starter Thread starter Persio Pucci
  • Start date Start date
P

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
 
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.
 
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
Click to expand...

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.
 
Hi,

All the respondants of your original value test against constant dates and
say
it works for me.

However you are trying to group data. One thing to check as an explaination
is to see what the minimum and maximum date/times you are grouping on.

Access may be counting hours from the the first data in the group when
grouping, i.e the first time is 08.31, so add an hour on to that and you get
09.31. So if this is true I'd expect all those with a time in 08.31 to
09.30:59
to be in one group and all in 09:31 to 10.30:59 in the next and so on.

This may not be what you want but it would explain whats going on.
One test may be to add a record which has a date/time such that it is the
minimum, so assuming that 8.31 is the minimum in your data, try adding
one with the time 8.00 and then trying again.

Regards,
Peter
 
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!
 
Back
Top