ASP Sort MS Access Query by Ascending Date

I have written a script which selects distinct dates from a database and populates them into a combo box.  See script below:

                        ' Get distinct date values
                        set rsD = Server.CreateObject("ADODB.Recordset")
                        response.write "<select name=date_select><option selected>None Selected</option>"
                        mSQL = "SELECT DISTINCT tbl_master.Date FROM tbl_master ORDER BY tbl_master.Date;"
                        rsD.open mSQL, fso
                        do while not rsD.eof
                            if not rsD.eof then
                                Response.Write ("<option value=" &rsD("Date")& ">" &rsD("Date") & "</option>")
                            end if
                            rsD.movenext
                        loop
                        response.write"</select>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"
                    %>

What happens, is the dates are sorted, but not really in ascending order.  See attached screen shot: Screen Shot


How can I sort these records in true ascending chronological order??
LVL 7
dimmergeekAsked:
Who is Participating?
 
tobzzzCommented:
Have you tried:
ORDER BY tbl_master.Date ASC
To force it into Ascending order (though I did think it did this by default)?

Also, is the tbl_master.Date column in the database a datatype of date or something else?

/ Tobzzz
0
 
dimmergeekAuthor Commented:
ASC worked; data type was text not date formatted as short date.

THANK YOU!
0
 
Dale FyeCommented:
Not sure how this would work correctly with a text data type.  "9/8/2010" will always be greater than "9/20/2010" with a text data type.  I think that should be:

ORDER BY Format(tbl_master.Date, "yyyy/mm/dd") ASC

to ensure consistent ascending order.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.