Sorting for Report by time

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
I have created a simple db to log my time each day. I run a report that can show what I did by category however, whenever I sort by the date field (which includes time) it doesn't recognize the hours and minutes aspect and only sort by the date and then by record number. Is there something quirky that I need to do to sort by the time in ascending order?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
If your date field is truly formatted as a date/time field, it should sort properly.
Are you sure that your field is not text?
Are you sure your sort hierarchy is set properly?
 
Upvote 0
I have created a simple db to log my time each day. I run a report that can show what I did by category however, whenever I sort by the date field (which includes time) it doesn't recognize the hours and minutes aspect and only sort by the date and then by record number. Is there something quirky that I need to do to sort by the time in ascending order?

When you created the Field do you have any default options that adds the Date() rather than Now() which includes time.
 
Upvote 0
To answer all the questions, it is formatted as Date/Time and I did try a sort both ascending and descending and yet I didn't see any change on the report. I thought the same as far as sorting by date that it would take the time into effect but it doesn't seem to be.

I know that I am using Now() and not Date() simply because everything is driven by time and not necessarily date. As a matter of fact, on my form, I only display the time in long time format anyways.
 
Upvote 0
Did you appy the sorting in the underlying query, or in the report itself (because you can set sorting on the report level also, and that would take precedence over how the underlying query is sorted)?

Also, are you using Grouping on your report?
If so, what is the precedence of that?
 
Upvote 0
I don't think I am sorting on the report level, just query, however that is something that I need to double check. I am using grouping and it is grouped by the type of activity. After that, it should give me a sort of time.


Hmm, wait a minute. I guess if there is grouping the sort is probably occuring at the report level and not at the query level. I'll be back in a sec after I check.
 
Upvote 0
Well, the report is pulling directly from the query, and when I run the query the results are in the correct order, but when they are in the report they are showing in descending order (PM times first, then AM) no matter how I specify the sort.

I might just need to rebuild the report.
 
Upvote 0
Just so you are aware -

You MUST set the sort in the REPORT'S SORTING AND GROUPING. Reports do NOT maintain the sort from a query.
 
Upvote 0
Hmmm, interesting. Question then - if my report is pulling from a query, if I try to go into the query builder in the report, all it is doing is editing the existing query. Do I need to drop the existing query that is outside of the report and rebuild it within the report?

This is a pretty simple project so no other reports are currently using the existing query.
 
Upvote 0
Hmmm, interesting. Question then - if my report is pulling from a query, if I try to go into the query builder in the report, all it is doing is editing the existing query. Do I need to drop the existing query that is outside of the report and rebuild it within the report?

This is a pretty simple project so no other reports are currently using the existing query.

No, you don't need to do anything with the query. The query is fine. But for sorting you go to the actual SORTING AND GROUPING of the report - not the query. When working with Access 2003 or prior, to go to the sorting and grouping you would go (while in design view of the report) to VIEW > SORTING AND GROUPING and in 2007 or 2010 you would just right click on the report (if I remember right) to select it, if it isn't already displayed at the bottom of the report area.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top