Link to home
Start Free TrialLog in
Avatar of dimmergeek
dimmergeekFlag for United States of America

asked on

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: User generated image


How can I sort these records in true ascending chronological order??
ASKER CERTIFIED SOLUTION
Avatar of tobzzz
tobzzz
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dimmergeek

ASKER

ASC worked; data type was text not date formatted as short date.

THANK YOU!
Avatar of Dale Fye
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.