Sorting problem (1 Viewer)

[nelslynn]

I have a text field containing an elapse time on a report... i.e. 12:30:20 (12 hours 30 minutes and 20 seconds). I want to sort on this field from fastest time to slowest, however, it is a text field and therefore sorts like this... 11:00:00, 12:30:20, 12:00:00, 9:00:00.

also, is there a way to sort a form on different columns... I want to put a button on each column on a form to click and have the form sort on this column. I know you can right click and sort, but I would like a more user friendly way to do this... thanks.

 

[Mile-O]

In the query that the report is getting the data from you could redefine the Time field using the TimeValue() function.

 

[nelslynn]

Thanks for the suggestion, but 13 hours converts to 1:00PM... I need it to stay in the format of 13:00:22.

Anyone else?

Also, any suggestions on the second part of my first post?

Thanks.

 

[Jon K]

First Part:

Try this setting in a column in the query grid, using the correct field name:-

Field: Right("00" & [FieldName],8)
Sort: Ascending


Second Part:

If the form is based on a table, try this code in the On Click event of a button.

Me.OrderByOn = True
Me.OrderBy = "FieldName"


If the form is based on a query, pass a new SQL statement with an Order By Clause to the form's RecordSource:-

Me.RecordSource = "Select * from [TableName] order by [FieldName]"