Solved

ASP Sort MS Access Query by Ascending Date

Posted on 2010-09-21
3
853 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 47

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

726 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