Cannot sort query on text field 1

[ZOR]

A table has a field called Start which is a text field. It should have been a time field as its content is values like 00:00:10:56. Problem arises now the user wants to run a listbox/report query having this fieldsorted in ascending order. Is there any way I can modify to sql to make it think its a numeric? Please note the field Start gets displayed as TimecodeIn. Would appreciate any help getting round the problem. Thanks

SELECT DISTINCT TXCLIPS.NName AS Name, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode
FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]))
ORDER BY TXCLIPS.Start;

 

[PHV]

its content is values like 00:00:10:56
So, it should sorts correctly.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZOR]

Having a second look at the list, you are correct, its the report thats throwing it all out.
I am using : Reports("TLG").RecordSource = Me.LP.RowSource

Where LP is the name of the list, which shows items in sorted order, but when the report gets shown its all over the place? Thanks

 

[PHV]

You may try this:
With Reports("TLG")
.RecordSource = Me!LP.RowSource
.OrderBy = "[Timecode In]"
.OrderByOn = True
End With


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZOR]

Perfect PHV, many thanks yet again.

 

[ZOR]

Still playing up. My listbox prior to the report does show inconsistant sorting on the Timecode In field, which as said earlier is a text field.

I am getting

01:12:29:01
00:08:40:02

With field set to ascending sort. The listbox query is:

SELECT M.Barcode, C.NName, M.SeriesName, C.Shot, C.Duration, C.Comments, M.Competition, C.Start AS [Timecode In]
FROM TXMASTERS AS M INNER JOIN TXCLIPS AS C ON M.ID1 = C.ID1
WHERE (((C.Start) Is Not Null))
ORDER BY C.Start;

Unfortunately I never designed the table and the methods the time goes in but it should have been a time field I think, and not a text field. Thanks

 

[ZOR]

Its Okay. Since found problem to be in other query SQL, so still fine. Thanks