Using ORDER BY and SQL syntax

[Aaron5]

I'm sorting a CSV file having data like this:

Lab,Bin,Group,Location,Time_In,Time_Out,Elapsed
1,11,Heart,Room 203,4/26/2015 9:46:08 PM,4/26/2015 9:51:38 PM,00:15:30
0,9,Lungs,Room 222,4/26/2015 10:06:12 PM,4/26/2015 10:26:08 PM,00:06:35

Specifically sorting by the Elapsed values using a common ADODB recordset, using this SQL string:
"Select * FROM " & "labData.csv" & " ORDER BY " & "Elapsed"

This works, except that using the above SQL converts a value like 00:15:30 (15 minutes, 30 seconds) to 12:15:30.
I want to leave these values as-is and not be formatted as time.

Any advice appreciated.

 

[strongm]

Not sure why you are getting the conversion you are seeing. I don't get that here. So this is probably caused by your regional time settings (or possibly by a schema.ini file; have you created one?)

Either way, it is a display issue only (the value is held correctly by JET), and you can display it the way you want with something like (assuming the recordset is called myrs:

MsgBox format(myrs("Elapsed"), "HH:mm:ss")

 

[Aaron5]

Thanks strongm.
No schema.ini file, but.

You had me thinking (about formatting) so I thought about the field in the DataReport I am sending the recordset items to, is causing this time format.
The field was set to DataFormat General, so I set it as Custom format, with "hh:mm:ss", Then VB6 changed my setting to 'Time' (HH:mm:ss) and it works perfectly.

The message box you suggested gives a type mismatch, but I need to see this in-transit value.(working on it)

Thanks a million.

 

[tedsmith]

Without a schema file I think you will find it will assume it is a date.
If you force it to be a string in a schema it will come out just as a string in the original form.

On my computer:-
Format("00:15:30", "HH:mm:ss") gives 00:15:30

Cvdate("00:15:30") gives 12:15:30 AM

 

[tedsmith]

I recently had a similar problem and this is how I made a schema from the CSV file
It was a timetable that had to be converted to a database, all fields being text

   'first make a HeaderString from the CSV file that contains only the headers (first line of the CSV file Eg Use Line Input)
    Dim HeaderArray()
    HeaderArray = Split(HeaderString, ",")    'make a small header 
    'first remove old Schema file ready for conversion process if exists
    On Error Resume Next
    Kill TimetableFolder & "schema.ini"
    On Error GoTo GeneralError
    
    'Make new Schema file for ADODB new table procedure based on CSV header
    Close #9
    Open TimetableFolder & "schema.ini" For Output As #9
    Print #9, "[" & CSVFileName & "]"
    Print #9, "Format = CSVDelimited"
    Print #9, "TextDelimiter = """
    Print #9, "MaxScanRows = 0"
    Print #9, "ColNameHeader = True"
    Print #9, "CharacterSet = 1252"
    'add all column names from first few fields of CSV file
    For a = 0 To UBound(HeaderArray)
        Print #9, "Col" & LTrim(a + 1) & " = " & Chr(34) & HeaderArray(a) & Chr(34) & " Text"
    Next
    Close #9