Unexpected sort results

[George]

Good Day all,



I am trying to sort the values in a field in a table, however only some of the values are sorted as expected.

The data type for the field is Date/Time and the property is medium time.

Here are the results for two officers in the same field, all sorted in ascending order:

can any one assist in explaining the difference.

Time Entered by Officer
7:59 AM
2:43 PM
3:43 PM
4:32 PM

Time Entered by Officer
8:00 AM
1:12 PM
2:11 PM
4:46 PM

[orange]

What were you expecting?
Please provide more info -- the table data, and query involved?

[George]

I am sorry; I gave what I would it to be. But her is the real output:

Time Entered by Officer
8:00 AM
1:12 PM
2:11 PM
4:46 PM

Time Entered by Officer
2:43 PM
3:43 PM
4:32 PM
7:59 AM

[orange]

You have to take AM and PM into consideration.
If PM then add 12 hours to the time.

[CJ_London]

a couple of other possibilities regarding the second set of data:

1. you are sorting on a formatted value of the time (which is a text type sort)
2. your data is actually date/time, not time alone and the 7:59 AM is the following day

[orange]

Further to Ajax's comment -- I think it would be helpful if you showed readers what you are dealing with.Show us the table design and query(s) involved.
It is evident that you can not deal with time alone without considering AM/PM. But I think Ajax has hit on a more fundamental issue and your really have to consider the full date and time and possibly whether you are dealing with a string representation.

[George]

Here is my table fields and a copy of the SQL query.

SELECT tblSignIn.*, tblSigninDetails.*
FROM tblSignIn LEFT JOIN tblSigninDetails ON tblSignIn.tblSignInID=[tblSigninDetails].TblsignInID;

[CJ_London]

OK, so which field are you sorting on? autodate or datecreated? - do either of these have default values and if so what are they?

[George]

"I am sorting on autodate which has no default values. My second sort is on "Arrival sign in" which has an alias:" Time entered by officer"

[CJ_London]

K, next we need to see the real values for the autodate field. Datetime is stored as a number, the date is before the decimal point and the time is after the decimal point and expressed as a percentage of the umber of seconds in the day (86400) and now (09:49) would be .409224537 - so can never be greater than 1.

copy and paste this code into the sql query window, run it and see the results

SELECT cdbl([Auto Date]) as DTime, *
FROM tblSignIn
WHERE cdbl([Auto Date])>=1

This should return no records - if it doesn't, then point 2 of my post #5 will be proven

[George]

I don't see how this code can go into the SQL Query window since I have joined tables

[George]

This is the code in the Sql window. Please see the attachment.

Notice that Bianca Hamblin's 'time entered by officer" are sorted except for 1 Value.

SELECT tblSignIn.*, tblSigninDetails.*
FROM tblSignIn LEFT JOIN tblSigninDetails ON tblSignIn.tblSignInID=[tblSigninDetails].TblsignInID;

[CJ_London]

don't see how this code can go into the SQL Query window since I have joined tables

? what has one to do with the other? I'm trying to determine the real value of the record and field in question and not what it is formatted as.

so a field with a real value of 42571.4751689815 (which is today at 11:26 AM) will show the same value as 42500.4751689815 (which is 21 days ago) when formatted as you have it on your report. See post #10 about how dates are stored.

If you don't want to do that, then if autotime is a field which has a default value of now(), try sorting by that column instead of time entered by officer and see if that solves the problem.

[George]

Thanks all. The discussion has given me an awareness of the Datetime datatypes and data and the technicalities. With this understanding I realize that my sort is not on the integer portion of the date number but rather on the decimal part, since I am not comparing dates by rather times in a day. Hence I need to strip away the integers and compare the decimals. Now in any single day all my decimal portions of the entries must be different for any single officer. Hence they should be comparable and sortable.

My question then is how can I separate out the decimal portion of the "date entered by officer"? My approach was to use and alias :Exp, by wrapping "date entered by officer" with timeValue function to get timeValue(date entered by officer). This works fine, however I realize it cannot take a sort.

Please let me know if I am on the correct track and where to go from here.

[CJ_London]

if you include the timevalue function in your query rather than a calculation in a control controlsource, you can then sort it in the report