Irregular sorting

[grenee]

Good Day all.

I have a field "Otime" under a group "OfficerTime" in a report.

Using the Group and sort button I have sorted on "Otime" newest to Oldest. "Otime" is of data type "medium time" eg. 8:30am.

The sort works well for all officer's entries except 1.

This make the evaluation of the problem very difficult.

Can anyone suggest the reason for this weird situation?

 

[arnelgp]

what do you mean by "except 1"?

 

[arnelgp]

you can try sorting it using Expression:

CDbl(TimeValue([yourTimeField]))

 

[grenee]

Thanks for the response.

Where can I write this expression?

 

[grenee]

what do you mean by "except 1"?

Of all the "Otime" sorts for each officer's entry there is only one officers' entries that are not sorted properly; all the others are.

 

[Cronk]

While your report might be formatted to show the data in a medium time format, the underlying data is a Date/Time field.

The exception could be explained by the fact there is a date as well as a time involved in the sort.

 

[grenee]

you can try sorting it using Expression:

CDbl(TimeValue([yourTimeField]))

Thanks All. This formula works. I placed it in the Query field.

 

[arnelgp]

hello again, sorry for late response.
i see you have sort things out using query.
but what i mean is you can go away without adding that in the query.
use the sort order of the report, and sort on Expression using the formula i gave.
goodluck.

 

[grenee]

hello again, sorry for late response.
i see you have sort things out using query.
but what i mean is you can go away without adding that in the query.
use the sort order of the report, and sort on Expression using the formula i gave.
goodluck.

No problem.

I did try it first in the expression builder but it would not accept it; so may be I was applying it incorrectly or in the wrong place. However for the record and future I would still like to do away with putting it in the query.

Do I place the formula in the source control property in the property sheet?

 

[arnelgp]

see this picture

 

[grenee]

see this picture

Thanks for the info; But can you say which version of Access you are using.
I am using ver 2007 so my page looks different from yours

 

[arnelgp]

im using A2013.
you can alway use the Reports Property: Order By. put the expression there.