• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

Access 2010 total current month data

I have a table (Statements)
2 of the fields are (ID) and (ReceivedOn) - date format short date

I would like to build a query to count the records ReceivedOn the current month.
ex: all of the September  

I tried this but it gave me all of the records, not just September:

SELECT Count(Statements.ID) AS CountOfID
FROM Statements
WHERE Exists (SELECT ID FROM Statements where DateDiff("m",ReceivedOn, Now)=0)
2 Solutions
Try this:

SELECT Count(Statements.ID) AS CountOfID
FROM Statements
WHERE  DateDiff("m",ReceivedOn, Now)=0
Dale FyeCommented:
Or, if you want more than September, then try:

SELECT Format([ReceivedOn], "yy mmm") as MonRcvd, Count([ID]) as RecCount
FROM Statements
GROUP BY Format([ReceivedOn], "yy mmm"), Format([ReceivedOn], "yymm")
ORDER BY Format([ReceivedOn], "yymm")

This will allow you to view the month like "12 Jan, 12 Feb, 12 Mar".  If you don't include the 2nd part of the Group By and the Order By clause , it will sort alphabetically.
NNiicckkAuthor Commented:
Thanks, both solutions worked great.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now