>>Hello,
>>
>>I am working with legacy data that has table of Events. I am simply using " SELECT * " from the source data table into a temp table with an ORDER BY Date, Time, Track, and Title. The resulting data order is almost correct except for the case where a "10:30 AM - 11:45 AM" event appears before the "7:45 AM - 8:45 AM" event for the same day in the output table.
>>
>>Naturally, I'm assuming this is a result of "1" coming before "7" in number order. I don't think that I can re-index or sort the results table as it is supposed to be in "print-ready" order for output to a comma-delimited, ASCII text file (using REPORT FORM <>.frx TO <>.txt ASCII). The customer wants to send this text file directly to the printing company for printing and binding of their conference manual.
>>
>>I've already groomed the source data tables for the Time field to insure all Caps and left justification. Also, I don't know if there is an acceptable way to code a function into the output report's Time field as it would most likely distort the print order of the data.
>>
>>Are there any TIME-related functions that might be employed inside the SQL statement that would handle this, that would output 7:45AM before 10:00AM in the Time column of the temp table?
>>
>>Thanks
>>
>>Mark
>
>Mark,
>Make them time datatype first. Assuming fieldname is evt :
>
>
select ctot("^2000/1/1 "+left(evt,at('-',evt))),* ;
> from myTable ;
> order by 1 ;
> into cursor myCursor
Notice that date used in expression has no importance and could be any valid date - hence if evt had a date attribute on another field then it would read like :
>ctot(dtoc(evtDate)+' '+left(evt,at('-',evt)))
>Cetin
Ooops. It would be :
ctot("^2000/1/1 "+left(evt,at('-',evt)-1))
Cetin