Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Crosstab column sort (Access 2000)

    I have produced a crosstab query with a range of dates as the column headings, but they are sorted alphabetically rather than chronologically. How can I correct this, or can I only do this in forms and reports based on the query?

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab column sort (Access 2000)

    Are your columns formatted as dates?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab column sort (Access 2000)

    Try using Format([DateField],"yyyymmdd") as column header field.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab column sort (Access 2000)

    Yes, that does the trick, thanks. Presumably if I want to use a format like "dd mmm yy", then I will need to do this in a form or report based on the query?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab column sort (Access 2000)

    I don't know enough about the structure of your query. Normally, dates will be sorted correctly (as dates), unless they are seen as text, then they will be sorted alphabetically. My previous reply was a workaround for the latter situation.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab column sort (Access 2000)

    Thanks. I discovered that although the actual field is a date/time field, the crosstab query wizard created the column heading field as an expression based on the actual field, which caused it to be sorted alphabetically. When I changed it from an expression back to a plain field, the columns were sorted chronologically as required.

Posting Permissions

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