Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ASP Sort MS Access Query by Ascending Date

Posted on 2010-09-21
3
Medium Priority
?
862 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
3 Comments
 
LVL 11

Accepted Solution

by:
tobzzz earned 500 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 49

Expert Comment

by:Dale Fye
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 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