Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sort by month in Crosstab (XP/2K)

    I have a crosstab query that the Row heading is "Format([dtburial],"m")" Dtburial is a date field. However it orders it 1,10,11, etc. I would like it sorted by the month. When I change the format to "mmmm" it sorts alphabetically.
    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sort by month in Crosstab (XP/2K)

    That seems a bit odd - is dtburial a true date field, or is it a text field? I have a very similar query with a statement "ReportMo: Format([dtReturnPeriod],"mmmm")" and it sorts in the correct month order.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort by month in Crosstab (XP/2K)

    date/time and formatted as short date. It is in a linked table. Weird.

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

    Re: Sort by month in Crosstab (XP/2K)

    The result of the Format function is a text value, so it will be sorted as text. Try using

    Month([dtBurial])

    as expression. The Month function returns a number, so it should sort correctly.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort by month in Crosstab (XP/2K)

    As usual that works. So why doesn't the crosstab query wizard do that? (dumb question)
    Thanks

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

    Re: Sort by month in Crosstab (XP/2K)

    > So why doesn't the crosstab query wizard do that?

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sort by month in Crosstab (XP/2K)

    There's an echo around here <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

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

    Re: Sort by month in Crosstab (XP/2K)

    As far as I can see, <!profile=Echo Swinford>Echo Swinford<!/profile> is not online... <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sort by month in Crosstab (XP/2K)

    He's obviously not lurking either. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

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

    Re: Sort by month in Crosstab (XP/2K)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Echo is a she - see 'Tis I.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sort by month in Crosstab (XP/2K)

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36> And a very attractive she she is !!

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

    Re: Sort by month in Crosstab (XP/2K)

    I'll echo that sentiment. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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