Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    8

    Unanswered: Sort by Month when text

    I am building a report in Access 2003 where my "date field" is the month of year in text (i.e. January, February etc). I need to sort chronologically not alphabetically. Can someone assist?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To sort dates successfully, you need to store them as dates. Did you know, if you stored them as dates using the datetime datatype, you'd be able to make them however you wanted on the reports (using the Format() function) and be able to order them correctly!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ORDER 
        BY INSTR('JanFebMarAprMayJunJulAugSepOctNovDec'
               , LEFT(urdatefld,3) )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    store dates as dates and the rest is simple
    you can pull out any element you wish by judicious use of SQL WHERE and ORCER BY sub clauses

    its exactly the same reason why you store numbers in numeric colums... stroing them as text look ssimple but is a real PIA int he long term
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's a clever solution Rudy, I'll give you that... But it's a kludge to bypass this initial roadblock, and you can guarentee it won't be the last
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, i appersheeate yore rimarks butt sumtymes yew go a littul overbored with yer advise

    pleaze can we wait two here from the original poster
    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
  •