Access 2007 time values do not sort correctly

[Lynda]

I hope you can help me with this one. My office recently converted to
Access 2007 from Access 2003. Since this happened, I cannot get time values
to sort correctly in reports. For example, we make appointments to visit
external businesses and print out a schedule of these appointments for our
staff who do these visits. What we have found is that the time values will
not sort correctly and we cannot find any way to make this happen.

Here is what a typical day is printing out as:

9/14/2007 Business XX 2:00 PM
Business AA 8:30 AM
Business CC 10:00 AM

Obviously, we want this to sort by the earliest times and have the PM time
last.

Any suggestions would be greatly appreciated.

Lynda Kinley

 

[KARL DEWEY]

You do realize that Access reports ignore sorts done in a query. You must
use the report Sorting and Grouping feature.

 

[Maverick]

What you said is true about how reports sort. However, there is one question
I must ask outside that issue: How are the times stored? If they are stored
as Text, they will sort differently than if they are stored as Time/Date. If
you are going to sort or calculated a date, it is best to store it as a date.
If it is stored as a date already, go with what Karl said.

 

[Lynda]

Hi:

The dates and times are store as Date/Time. The time is formatted as a
medium date.

I did try the sorting and grouping feature in the report, but that did not
help. The date sorted correctly, but the time did not.

Lynda

 

[Lynda]

Sorry, I mean the time values are formatted as medium time, not medium date.

 

[Michael J. Strickland]

You do realize that Access reports ignore sorts done in a query. You
must
use the report Sorting and Grouping feature.

Huh?

I haven't noticed Access ignoring my query sorts when printing reports.

Obviously, report sorts will override query sorts. However, as long as I
have specified the query as the record source and have no report sorts
defined, Access prints my reports in query sort order.


--

 

[KARL DEWEY]

What other sorts besides date and time do you have?

In what order are your sorts - date, action, time, etc.

 

[John Spencer]

Lynda,

Please post the query that you are using as the source for the report.
If you are using the FORMAT function in the query, then the data is
being turned into a STRING.

Format([DateTimeField],"Medium Time") returns a STRING that looks like a
time.

Apply the format using the format property of the control that is
displaying the time

Control
Name: Show Time
Control Source: NameOfDateTimeField
Format: Medium Time


If you really feel that you need the time calculated separately, then use

TimeValue([DateTimeField])


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Lynda]

Hi All:

Thanks for your help, but there is still no resolution to the problem. My
query that is behind the report does not sort anything nor does it format
anything. All of that is done in the sorting and grouping of the report.

I am really at a loss. In the report, I group on the employeeid, group on
eventdate, then sort on the time of the event. That is all.

So if anyone has any good ideas of what is going on, I would appreciate it.
In no way is anything being converted to text.

Lynda

 

[Lynda]

Thanks Bob, I tried your solution and that worked perfectly! I would never
have thought of doing that. Thanks so much!!!!

Lynda Kinley