In a table, I have a column which is date and time (06/03/2007 11:35)

I need ORDER BY datecolumn, title so that if there are two entries on the same date (at different times) they will be ordered by the titles.

Considered:

ORDER BY LEFT(datecolumn, 10), title

But thought that there must be a 'more correct' way to do this.

Regards,

Sean Anderson

__________________
Regards,

Sean Anderson

I am assuming that this is a query.

In the Query Designer, put the DateColumn to the left of the Title column, then order the datecolumn, probably descending, and then order the Title column Ascending. Access will order the left-most column first, then the next one to the right, and so on. So you can extend this for the whole query.

Did that help?


mmcdonal

BTW, I see you have this question for a table. I don't think you can do this in a table, but you shouldn't be displaying data in a table anyway. Create a datasheet form, and base it on the query. Then show the user the datasheet, not the table. (design issues).

HTH

mmcdonal

I don't think that works.

For example, the two columns contain the following, along with the expected order:

04/03/2007 08:00 - A (1st)
05/03/2007 10:30 - C (4rd)
05/03/2007 10:35 - A (2st)
05/03/2007 10:40 - B (3nd)
06/03/2007 09:45 - A (5th)

If you order by DATE, then TITLE, you get the order that is shown above, rather than the order defined in the parenthesis and you will see that the entrys for the 05/03/2007 are out of order.

Regards,

Sean Anderson

{QUOTE: BTW, I see you have this question for a table. I don't think you can do this in a table, but you shouldn't be displaying data in a table anyway. Create a datasheet form, and base it on the query. Then show the user the datasheet, not the table. (design issues).]

Sorry, poor terminology on my part. The end result is that the SQL Query will be used in an ASP page.

Regards,

Sean Anderson

Hi,
Use below query it will solve your requirement. Thanks to You, Because of your question I can share my experience with you.

NAME GRADE
-------------------------------
DEEPAK B (2nd)
KASHYAP A (1st)
KASHYAP B (2nd)
KASHYAP A (3rd)
SUSHIM C (1st)

SELECT tbM.Name, tbM.Grade, tbM.List
FROM tbM
ORDER BY tbM.Name, MID(tbM.Grade,4,1);

Regards,
KNSHARMA

But you have used a NAME for the first column, rather than a DATE.

I see what you have done, but there were only two columns in my example; the DATE, a TEXT column. The part where I have (1st) at the end, that was just to signify the order in which the results are expected to appear.

So for my table:

04/03/2007 08:00 - A
05/03/2007 10:30 - C
05/03/2007 10:35 - A
05/03/2007 10:40 - B
06/03/2007 09:45 - A

I am looking for this order:

04/03/2007 08:00 - A
05/03/2007 10:35 - A
05/03/2007 10:40 - B
05/03/2007 10:30 - C
06/03/2007 09:45 - A

Hope this clarifies the problem.

Regards,

Sean Anderson

The problem I'm seeing is that you want to sort only on the date portion of the date field when the date portion contains both date and time. If you look, the date field is sorted properly considering both date and time sort first in the order it's being sorted in.

To accommodate for this your query would have to take this into account.

This is the SQL I would suggest using:
---------------------------------------------------------------------------------------------------------
Select * From My_Table Order By Format([Date_Time], "mm/dd/yyyy"), [Letter_Grade];
---------------------------------------------------------------------------------------------------------

If for whatever reason this doesn't work for you as it did for me (I got the correct sort order you're looking for) then I'd suggest creating a field that holds only the date portion and sort on that field instead.

Hope this helps and explains what is happening for you.

P.S. Note the reason I'd use Format() if it works instead of left() is that format will extract the date the same regardless to how it's displayed in the actual table and doesn't require a length that may vary.

Perfect idea.

Didn't realise that there was a "format" function available.

Thank you very much.

Regards,

Sean Anderson