Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting a report

    I need some advice on sorting a report. I have a table of birthdays, just the name (first & last together), month and the day. I'd like to sort these by the month, beginning with Jan, Feb, etc. But when I chose month and sort ascending, it sorts them alphabetically, starting with April, then August.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting a report

    It sounds like you've stored the month as text, i.e., "Jan". In that case, the only thing the program can do is sort it alphabetically, since it sees it as text, which is what it is. If you store the month as a number, you can sort it chronologically.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a report

    You'll need to make sure that the birthdate is entered as a date data type. If you enter month as a text type, Access will not recognize that it's actually a month.

    You could create a new field in the table called Birthdate, using Date as data type. Then, create a query from the table. You'll want to sort the query by Birthdate (Ascending). Also, create a field in the Query with the following expression in the Field row:<pre>BirthdayMonth: Format([Birthdate],"mmmm")</pre>

    Then you can create your report from the query - grouping on each value from the BirthdayMonth field.

    Post back if you have any more problems or questions. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a report

    Isn't your format command going to create a field which contains "January", "February", ...? Isn't that Going to sort as "April", "August", ...? Shouldn't the format pattern be "mm" to get the desired order?

    In Excel there is a Month() function that returns the month number (1 - 12). Does that exist in Access, and if so I would create a field to sort on like this:

    BirthMonth: Month([BirthDate])
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a report

    If you sort on the month field it will sort alphabetically ("April, August...."). However you would sort the query on the Birthdate field - then the Month name field will automatically be in order (based on the date order of the birthdates). <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  6. #6
    New Lounger
    Join Date
    Mar 2001
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a report

    Thanks for all the input. Part of my problem is that I don't have complete birthdays, just month and days. I added a field, called month# and manually entered 1 for Jan, etc. Then I sorted by this field. It seems like an inelegant solution but with only about 150 records it wasn't too time consuming.

Posting Permissions

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