Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: Date Not Sorting...

    Sry bout this sorta newbie question...usually i dont ask these kinda questions..

    have a function which returns a string, which is a date;
    i have tried returning a date too;

    i use the function in a query, where it outputs a date;
    when i try to sort by this date, it doesn't sort right;

    e.g.

    In ascending sort, this is what it displays:

    19-Apr-04
    19-Apr-03
    19-Aug-04
    21-May-04
    26-Mar-04
    27-Apr-04
    27-Jun-04
    27-Mar-03
    27-Mar-04

    it sorts by the first two digits, and doesn't take into consideration the month nor the year

    how would i solve this? as i mentioned before, i have tried outputting both to string, to date, and string which is formatted to format(output, "Medium Date")

    thx

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    i stripped the query down to just this field, and tried sorting ascending (in query design..) and it still shows the same results;

    note:the function returns a string;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    lol there is a reason why i haven't shown u they query; its confidential data?

    even if i did, there is a whole lot of tables and relationships in there that wouldn't make sense, but do not affect the workings of this function or its output;

    the only thing you have to know thus far is
    a. the function checks in through ADO to a table, finds the date from a specific field, checks a few other criteria and then outputs this date;
    b. i placed this date into the query design field, and put it to sorting ascending; as you can see, the data is not sorted, or is sorted haphazardly;

    c. in datasheet view, i even tried sorting by the field itself by right clicking the column, and it still shows the same output; this proves that the problem does not lie in the query structure nor the tables (which is yet another reason why i didn't have to show the query). It lies in the code.

    Thank you.

  6. #6
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    In query design, her's how it looks;

    The function prototype is :

    Function DueDate(ID as integer) As string (tried date too)
    .
    ..
    ..
    End Function

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i shall simply give you some guidelines

    do not sort by the string produced by the function

    instead, sort by the actual date/time field

    and if you don't have an actual date/time field holding your date, and it's actually an integer date (which is what i'm guessing from your function definition), then just sort by the integer

    if that doesn't work, write another function to translate the XXX month into an actual month number, re-assemble a date/time value using CDate, and sort on that

    best i could do without knowing what you're doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    The function, uses the ID number to go into a specific table which has all the due dates associated with this ID#. The due dates are stored in a Date/Time field;

    The function outputs the lowest date associaed with that ID#;
    However, sometimes, there are no due dates associated with the ID#; hence, i cannot output the function as a date variable since it will cause an error (cannot output "" as date, but there are ways around it)

    i have a temporary solution which works for the mean time, (dissect the string into three parts, delimiting the "/" and
    days+months*30+years*30*12;

    this outputs an integer which i can then sort by for ascending due dates;
    it sorta solves the problem, but i still wanna know why it diesnlt work;

    will try the CDate function;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    based on this last piece of information, you should be using a join to the due dates table rather than a function

    this will not only allow you to retrieve the date in both formats (string and date/time) but it should speed up your query too

    but you seem intent on keeping your stuff a secret and using a function, so the best i can do is wish you good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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