Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query by month (2000)

    I'm trying to find out the number of records I have per month by a date field. I have made a simple query that has two fields: The date, which I have changed the format in the query to month/year, and the record number. I have turned totals on and grouped by date and count record numbers. I thought this would group the dates by month, but it's grouping them by day and showing month/year.

    How can I get it to group by month?

    Thanks,
    Eric

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

    Re: Query by month (2000)

    Formatting a date is only skin-deep, it doesn't change the underlying data. When you group by it, Access looks at the underlying data, not at the formatting.

    There are (at least) two ways to count by month:

    1. In a query. Replace the date field by an expression:

    MonthOfDateateSerial(Year([DateField]),Month([DateField]),1)

    where DateField must be replaced by the actual name of your date field. This "maps" every date to the first day in its month. You can now group by MonthOfDate.

    2. In a report. Reports have the capability to group date fields by day, week, month, quarter or year. The Report Wizard is the easiest way to accomplish this. N.B. You don't need to create a query like the above for this; the Report Wizard will take care of all the nitty gritty details.

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by month (2000)

    Thanks. I still had a problem. I was getting a data mismatch error. So I did two separate columns using the Year function in one and the Month function in the other with the results I wanted. Thanks for pointing me in the right direction.

    Eric

Posting Permissions

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