Results 1 to 6 of 6

Thread: Order By Issue

  1. #1
    Join Date
    Jan 2010
    Posts
    44

    Unanswered: Order By Issue

    Now I have my order by working kinda right. The only issue is I am doing a Max Function on a date and it put the dates at the top, but it doesn't put them in order. It does the following:
    4/1/2010
    4/12/2010
    4/13/2010
    4/4/2010
    4/4/2010

    Anyone know how to figure that out to do the following?
    4/1/2010
    4/4/2010
    4/4/2010
    4/12/2010
    4/13/2010

  2. #2
    Join Date
    Jan 2010
    Posts
    44
    Do I have to change something in my table that holds the date?

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can create an index on the field or simply just sort it in the table, save when closing or in any queries you design sort it by the date field in the row (Sort) that allows you to put ascending or descending.

    Make sure it's a DATE type field though in your table design and not a text type field. Date values should NOT (nor ever) be in a text type field (unless it's a very, very unusual situation.)
    Last edited by pkstormy; 04-28-10 at 22:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2010
    Posts
    44
    Its a date/time field type and i sorted it in the table and it still does the 4/4/2010 after the 4/13/2010.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok. You can try another trick in your query which is create expressions for the month, another expression for the day, and another for the year.

    ex:
    ExpMonth: Format([MyDateField],"mm")
    ExpDay: Format([MyDateField],"dd")
    ExpYear: Format([MyDateField],"yyyy")

    and sort accordingly.

    Not sure how this works utilizing the Max in the query but give it a try. It might be the Max itself which is causing the problem so try it in a normal query which doesn't utilize the summarization and Max function.

    You could also possibly try right clicking on the column itself and format it as a short date.
    Last edited by pkstormy; 04-28-10 at 23:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jan 2010
    Posts
    44
    I formatted my Sale_Date in my 'Saved Query' and then formatted the order by in my 2nd query and it works :-) Thanks for the help.

    First Query:
    MAX(format(SALE_DATE,"mm/dd/yyyy")) as 'Last Sale'

    Second Query my report is tied to:
    ORDER BY Format(['Last Sale'],"mm/dd/yyyy");

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •