Sorting Access Data (date times in the wrong format)

Question

Problem:

Some of the fields in my table have the wrong format for a date, they're formatted using the .ToString() Method in C# but i made a typo and entered it like this : MM/dd/yyyy hh:mm/ss tt

Now, if you look closely you see MM/dd/yyyy hh:mm ----> '/' <--- ss

which is suppose to be a ':'

I then preceded to fix it in my c# code, (meaning some of the values are formatted: MM/dd/yyyy hh:mm:ss tt)

The problem is, i'm trying to select values from the table in order of Date (which i can't just Sort by 'Datefieldnamehere') because some of the fields are in the wrong format with a '/'

---

What i have Tried:

Now, i figured since the time is always in the same spot (and that's what matters to me) i could just take the substring of the numbers in the time and order them; first for AM then for PM

I could Order By MID([ColumnName],11,2) , MID([ColumnName],14,2), MID([ColumnName],16,2) because every date regardless of the delimiter is always in the format M/dd/yyyy hh/mm/ss tt

So I Tried:

---

(
SELECT SN, StatusCode, Time, Mid(Time,14,2) + ':'+ Mid(Time,17,2) AS TTI
FROM OrderStatus
WHERE StatusCode = 'Finished' and Left(Time,10) = '4/20/2012'
AND Time LIKE '*AM'
ORDER BY Val(Mid([Time],11,2)) DESC
)
UNION ALL (
SELECT SN, StatusCode, Time, Mid(Time,14,2) + ':'+ Mid(Time,17,2) AS TTI
FROM OrderStatus
WHERE StatusCode = 'Finished' and Left(Time,10) = '4/20/2012'
AND Time LIKE '*PM'
ORDER BY Val(Mid([Time],11,2)) DESC
);

---

Just to see if that would Order it by hour but it doesn't it gives me this :

As you can see it goes (in the hour field) 01 then 02 and then it goes back to 01... ?

Anyway there's a similar question here That i have put a bounty on, you will earn the bounty if you answer this question or that question, this would be useful to know for other scenarios as well

Edit: The Question that i really want answered how to sort by a Substring of a string; sorry for the late edit i could of swore i changed this already, say i want to sort by a single digit of any string / date / anything i was wondering how i can do something like: Order By Val(Mid(ColumnName,StartPos,EndPos))

Note: That Order By gives completely wrong results.

Answer 1

My instinct is to create a function and run it once in an Access UPDATE statement to convert the stored values to match the desired format.

The quick & dirty function below copes with both single and two digit months. It requires Access 2000 or later.

? FixTimeData("4/20/2012 01:34/09 PM")
4/20/2012 01:34:09 PM
? FixTimeData("12/20/2012 01:34/09 PM")
12/20/2012 01:34:09 PM

Public Function FixTimeData(ByVal pIn As String) As String
    Dim astrPieces() As String
    Dim strOut As String
    astrPieces = Split(pIn, " ")
    strOut = astrPieces(0) & " " & _
        Replace(astrPieces(1), "/", ":") & " " & _
        astrPieces(2)
    FixTimeData = strOut
End Function

Then an UPDATE statement similar to this ...

UPDATE OrderStatus
SET time_field = FixTimeData(time_field)
WHERE time_field Like "*/*/*/*";

If you run the statement under ADO instead of DAO, change to ANSI wild card characters in the WHERE clause.

WHERE time_field Like "%/%/%/%";

Or use ANSI wild cards with ALike if you want the same statement to work under either ADO or DAO.

WHERE time_field ALike "%/%/%/%";

I used time_field as the field name where you used Time because Time is a reserved word. If you can't change the field name, enclose it with square brackets in your query.

Edit: My intention was to fix the data so you can then sort reliably based on Right(time_field, 11)

Edit2: To sort based on the "upper" minute digit, see if you can build on this approach, which assumes you have fixed the stored date/time strings:

? Left(Format(Minute("4/23/2012 04:02:40 PM"), "00"), 1)
0
? Left(Format(Minute("4/23/2012 04:12:40 PM"), "00"), 1)
1
? Left(Format(Minute("4/23/2012 04:22:40 PM"), "00"), 1)
2

Although the Minute() function accepts a string, I would likely use CDate() to explicitly transform the string to a Date/Time before feeding it to Minute().

So ... if I haven't gone completely astray ... try that approach in a query like this:

SELECT
    Left(Format(Minute(CDate(time_field)), "00"), 1) AS upper_minute,
    OrderStatus. *
FROM OrderStatus
ORDER BY 1;