×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Using ORDER BY and SQL syntax

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!

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