6 Replies Latest reply on Mar 3, 2016 9:31 PM by jatowle

    filter by month

    jatowle

      hi all

       

      I have a layout with a date field (month/day/year) I am tying to do a sub summary  count of records in the layout by month. I can get my summary to give me total number of records I just want one more level to give me total number of invoices per month

       

      Thanks

      Jeff Towle

        • 1. Re: filter by month
          jbrown

          Evening.

           

          I would set up another field that pulls the month from the date field: MonthNo (YOUR TABLE::DateField) as a calculation. Maybe have even another calculated field that pulls the month name:  MonthName (YourTable::DateField). That way you have the month name to use in your report

           

          Then you can use this field in your subsummary break above your body of records.

          On the report itself, in the sub summary row, put the MonthName field, but SORT BY the MonthNo field.

           

          Your summary field, if finding the total of records and placed in a sub summary sorted by the MonthNo field, will count those records in each month.

          • 2. Re: filter by month
            beverly

            Howdy! I used to have a "month" field and then I ran into the problem with that. If I have records spanning year (or more), then sorting/summaries were not correct. I now have a yr_mon field (auto-enter) with

            Year(myDate) & "_" & Right("00" & Month(myDate) ; 2)

             

            I can get the month name by formatting myDate in the summary part to just show the month name, so don't even have a separate field for that.

             

            This wonder-field can is often used in charts as well. I can change the "label" to show the 'MonthName YYYY' by simple parsing back out while retaining the correct sorting and grouping of the records.

             

            beverly

            • 3. Re: filter by month
              jbrown

              That's a cool idea. I didn't think of that.

               

              Another wrinkle

              In a current client's database, I have to find the current month and then put that month at the top of the report, and then sort the rest of the months in logical order. that was a bear to figure out.

               

              I think I picked up the technique from here in the community.

              • 4. Re: filter by month
                beverly

                That's why a YYYY_MM format. It's "text" and alpha-sortable.

                beverly

                • 5. Re: filter by month
                  jatowle

                  Jeremy,

                   

                  thanks this worked

                  • 6. Re: filter by month
                    jatowle

                    Beverly,

                    I never thought of that either as this is a brand new database and I only have data from 1016 in so far but I never thought of what will happen when I hit 2017. I will try your idea tomorrow

                     

                    Jeff