Results 1 to 3 of 3
  1. #1
    JohnS is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    4

    Smile Sorting in a Query (Dates)

    So I have a "gazillion" dates in my "Date" Column

    01/01/09

    I wanted to extract the year and month, so isued the =year[x] and =month[x] fx and then concatenanted the 2 =year[x]&month[x].

    This doesn't allow for proper sorting

    when I sort I get the following

    20091


    200910
    200911
    200912
    20092
    ect

    to work around this issue I used the followin f(x)
    MONTH1: IIf(Len(Month([c-date]))=1,0 & Month([c-date]),Month([c-date]))

    200901
    200902
    200903
    200904
    (...)
    200910
    200911
    200912

    This fixed my issue, but was wondering is there was a more efficient solution?

  2. #2
    SoftwareMatters is offline Access VBA Developers
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    274
    Can't you just put a sort on the actual date column?

  3. #3
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    If you must do it this way, then

    Alias:Format([c-Date],"yyyymm")


    BTW how many is a gazillion?
    David

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need Sorting Help
    By rbpd5015 in forum Access
    Replies: 1
    Last Post: 09-28-2009, 07:39 PM
  2. Update query for 12/30/1899 dates
    By P5C768 in forum Queries
    Replies: 8
    Last Post: 09-25-2009, 09:16 AM
  3. Matching Query Dates
    By rgwynne in forum Queries
    Replies: 1
    Last Post: 08-13-2009, 05:23 AM
  4. Sorting in a form
    By PaulDouglas in forum Forms
    Replies: 3
    Last Post: 07-27-2006, 08:28 AM
  5. Is this a Query or Sorting problem?
    By bwrobel in forum Queries
    Replies: 2
    Last Post: 07-14-2006, 08:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums