INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using ORDER BY and SQL syntax

Using ORDER BY and SQL syntax

(OP)
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.

RE: Using ORDER BY and SQL syntax

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")

RE: Using ORDER BY and SQL syntax

(OP)
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.

RE: Using ORDER BY and SQL syntax

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

RE: Using ORDER BY and SQL syntax

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

CODE -->

'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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close