Grouping by Hour doesn't work!! 1

[jolby]

Whenever I select group by hour in sorting and grouping, it returns a split between two hours. I.e. it would group between 7.30 and 8.30, instead of between 7.00 and 7.59. Is there any way to fix this?

Cheers

Joel

 

[dhookom]

Could you provide a few sample records and show where your groups are being split?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jolby]

Given The sample Input, with Time as a Date/Time field with format "Short Time"

IDA Quantity Date Time
0112843 40 30/05/2005 08:28
0112844 40 30/05/2005 08:37
0112845 40 30/05/2005 08:39
0112847 40 30/05/2005 08:46
0112849 40 30/05/2005 08:52
0112850 40 30/05/2005 09:01
0112851 40 30/05/2005 09:12
0112852 40 30/05/2005 09:24
0112853 40 30/05/2005 09:27
0112854 40 30/05/2005 09:36
0112855 40 30/05/2005 09:42

The report would look like:

0112843 40 30/05/2005 08:28
Sum 40

0112844 40 30/05/2005 08:37
0112845 40 30/05/2005 08:39
0112847 40 30/05/2005 08:46
0112849 40 30/05/2005 08:52
0112850 40 30/05/2005 09:01
0112851 40 30/05/2005 09:12
0112852 40 30/05/2005 09:24
0112853 40 30/05/2005 09:27
Sum 320

0112854 40 30/05/2005 09:36
0112855 40 30/05/2005 09:42
Sum 80

The only way I seem to get around this is if I change the Time format to a text field, and group by the first two numbers.

 

[KenReay]

Hi

Less drastic than changing the field to text, how about Grouping on Format(Time,"hh")

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dhookom]

Jolby,
I have never seen an issue with sorting and grouping by time increments. Are there any other levels of sorting prior to the hour increment sort?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jolby]

Ken
Format doesn't seem to have any effect on the way it is grouping.

dhookom
There isn't any other levels of sorting. At first I was running my report off a query that selected a range of dates and times, when I first noticed it. But I have also tried it with some sample data in a table with only ID and Time. Everything I try still gives me the same result.

 

[dhookom]

Does your report's SQL apply a format to your date/time field? Are you using the most recent service packs of Office? What version of Access are you using?

Do you have a Date field and a Time field? Are these named Date and Time? If so, the names are possibly an issue since they are names of functions.

I just tested this and had similar issues. I changed my sorting to
=Hour([Time]) Each Value



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jolby]

dhookom
Thanks, sorting on Hour([Time]) - Each Value worked fine. In regards to the other questions, I am using [process time] as my time field. I am using Access 2000 I think (i'm not at work right now).

I'm glad it wasn't just me having this issue.

Cheers
Joel