Solved

ASP Sort MS Access Query by Ascending Date

Posted on 2010-09-21
3
854 Views
Last Modified: 2012-06-27
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??
0
Comment
Question by:dimmergeek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Accepted Solution

by:
tobzzz earned 125 total points
ID: 33725837
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
 
LVL 7

Author Closing Comment

by:dimmergeek
ID: 33725882
ASC worked; data type was text not date formatted as short date.

THANK YOU!
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 33726059
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question