dimmergeek
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>&n bsp; &nbs p; "
%>
What happens, is the dates are sorted, but not really in ascending order. See attached screen shot:
How can I sort these records in true ascending chronological order??
' Get distinct date values
set rsD = Server.CreateObject("ADODB
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>&n
%>
What happens, is the dates are sorted, but not really in ascending order. See attached screen shot:
How can I sort these records in true ascending chronological order??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ORDER BY Format(tbl_master.Date, "yyyy/mm/dd") ASC
to ensure consistent ascending order.
ASKER
THANK YOU!