Count by Month NotGood[ at ]All 12/26/2008 9:58:00 PM I have a query that gives me totals by date. I would like to ge the totals by month but the field is a date field (12/12/2008). How would I remove the day and get a count of how many for Dec 2008? Thanks -- NotGood[ at ]All

Re: Count by Month "Pete D." <pduffy211AT[ at ]coxDOT.net> 12/26/2008 10:13:07 PM Read dateadd() in help then post back with questions. "NotGood[ at ]All" <NotGoodAll[ at ]discussions.microsoft.com> wrote in message news:C7B7B2E5-1BD7-4C58-B2BB-A6345B4AD486[ at ]microsoft.com... [Quoted Text] >I have a query that gives me totals by date. I would like to ge the totals > by month but the field is a date field (12/12/2008). How would I remove > the > day and get a count of how many for Dec 2008? > > Thanks > -- > NotGood[ at ]All

Re: Count by Month "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/26/2008 10:52:22 PM Presumably your query currently is something like: SELECT MyDateField, Count(*) FROM MyTable GROUP BY MyDateField Change that to SELECT Format(MyDateField, "yyyy\-mm"), Count(*) FROM MyTable GROUP BY Format(MyDateField, "yyyy\-mm") or, for a single month only SELECT "Dec 2008", Count(*) FROM MyTable WHERE MyDateField BETWEEN #2008-12-01# AND #2008-12-31# -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "NotGood[ at ]All" <NotGoodAll[ at ]discussions.microsoft.com> wrote in message news:C7B7B2E5-1BD7-4C58-B2BB-A6345B4AD486[ at ]microsoft.com... [Quoted Text] >I have a query that gives me totals by date. I would like to ge the totals > by month but the field is a date field (12/12/2008). How would I remove > the > day and get a count of how many for Dec 2008? > > Thanks > -- > NotGood[ at ]All

Re: Count by Month NotGood[ at ]All 12/26/2008 10:55:00 PM Pete, thank you for responding. What I want to do is get a total by month, I have a query that gives me totals by day (12/24/2008) =3 and so on. What I want to do is use this query, take out the 24 and get a total by month 12/2008 = 45 -- NotGood[ at ]All "Pete D." wrote: [Quoted Text] > Read dateadd() in help then post back with questions. > > "NotGood[ at ]All" <NotGoodAll[ at ]discussions.microsoft.com> wrote in message > news:C7B7B2E5-1BD7-4C58-B2BB-A6345B4AD486[ at ]microsoft.com... > >I have a query that gives me totals by date. I would like to ge the totals > > by month but the field is a date field (12/12/2008). How would I remove > > the > > day and get a count of how many for Dec 2008? > > > > Thanks > > -- > > NotGood[ at ]All > > >

Re: Count by Month "Pete D." <pduffy211AT[ at ]coxDOT.net> 12/27/2008 3:13:46 PM Sorry I didn't respond, for some reason my flag on this didn't stick so I did not see you responded. Anyway, Doug answered. "NotGood[ at ]All" <NotGoodAll[ at ]discussions.microsoft.com> wrote in message news:86ECEC2D-2A74-4196-9CEE-72297EFE86FE[ at ]microsoft.com... [Quoted Text] > Pete, thank you for responding. What I want to do is get a total by > month, I > have a query that gives me totals by day (12/24/2008) =3 and so on. What > I > want to do is use this query, take out the 24 and get a total by month > 12/2008 = 45 > -- > NotGood[ at ]All > > > "Pete D." wrote: > >> Read dateadd() in help then post back with questions. >> >> "NotGood[ at ]All" <NotGoodAll[ at ]discussions.microsoft.com> wrote in message >> news:C7B7B2E5-1BD7-4C58-B2BB-A6345B4AD486[ at ]microsoft.com... >> >I have a query that gives me totals by date. I would like to ge the >> >totals >> > by month but the field is a date field (12/12/2008). How would I >> > remove >> > the >> > day and get a count of how many for Dec 2008? >> > >> > Thanks >> > -- >> > NotGood[ at ]All >> >> >>

Re: Count by Month "Viarum via AccessMonster.com" <u48365[ at ]uwe> 12/28/2008 10:20:20 AM Just a tip: SELECT MyDateField-Day(MyDateField)+1 AS MyMonth, Count(*) FROM MyTable GROUP BY MyDateField-Day(MyDateField)+1 The advantage is that MyMonth is a date type and can be used easily elsewhere. When used in a graph e.g. you can still format it with Format(MyMonth, "yyyy\- mm") -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1