Unable to sort time values in table

[George]

Good Day all,



I have a field "Time Entered by Officer" which is of data type Date/Time. I realize that my code shown below does not sort the field for all the values. I know that somewhere I have to use the TimeValue function; this I tried but it does not work.

In my code below the problem lies with the ORDER BY ([Time Entered by Officer]) section. The code as given works well except that some of the values are not sorted. When I Try TimeValue([Time Entered by Officer]), I get the error message "data type mismatched in criteria expression"

Can any one assist?

 Do
        strfilter = " [OfficerName] ='" & OfficerName & "' And DateValue([Auto Date]) = # " & dynamicDate & "#" & " ORDER BY ([Time Entered by Officer])"       
          
       Set rs = CurrentDb.OpenRecordset _
       ("SELECT tblSignIn.*, [Time Entered by Officer] FROM tblSignIn LEFT JOIN tblSigninDetails ON tblSignIn.[tblSignInID] = tblSigninDetails.TblsignInID WHERE " & strfilter)

[Bob Fitz]

In the table "tblSigninDetails" what is the data type of the field "Time Entered by Officer"

[George]

In the table "tblSigninDetails" the data type of the field "Time Entered by Officer" is Date/time and medium time format.

The "Time Entered by Officer" field is in the "tblSigninDetails" only; the field does not exist in the "tblSignin" Table.

[Bob Fitz]

Can you post a copy of the db with some dummy data that illustrates the problem

[George]

tblSigninID	Punctuality	Leave	OfficerName	Note	Punctuality Override	Auto Date	Schedule Arrival	Lunch Sign out	Arrival Sign In	Departure Sign out	Early Departure	Departure Override	Lunch time Sign in	DateCreated	LastEdited	SupervisorOverRide
203	Late		Bianca Hamblin		Yes	Friday, July 01, 2016	8:15:00 AM					No				No
316	Early		Bianca Hamblin		Yes	Monday, July 04, 2016	8:15:00 AM					No		Monday, July 04, 2016		No
315	Early		Kellie Herbert		Yes	Monday, July 04, 2016	8:15:00 AM					No		Monday, July 04, 2016		No

[George]

ID	TblsignInID	DateEnteredByOfficer	Time Entered by Officer	Officer Notes	In/Out	Supervisior Comment	Auto Time	AutoEntry	Lunch	DateCreated	AutoSave
897	203	01-Jul-16	2:43 PM		OUT		2:43 PM		To Lunch	7/1/2016 2:43:31 PM
898	203	01-Jul-16	3:43 PM		IN		4:32 PM		From Lunch	7/1/2016 4:32:28 PM
899	203	01-Jul-16	4:32 PM		OUT		4:32 PM			7/1/2016 4:32:43 PM
891	203	01-Jul-16	7:59 AM		IN		7:59 AM			7/1/2016 7:59:11 AM	AutoSave

[George]

I could not get both tables save in the same window. The first table is the "tblSignIn" and the second is "tblSignInDetails".

Notice that in the "tblSigninDetails" in the "Time Entered by Officer" Field, the values are sorted in ascending order and yet the value 7:59 AM comes unexplained at the bottom of the list when it should actually be at the top.

[George]

Sorry for my Bother. I found the solution.

One of records in the "Time entered by Officer" field had no values, it was NULL