Unanswered: query criteria- expression to return previous month's data
Creating a query to pull records within the previous month, and came up with this criteria for the date field from examples I found on the web:
Between Date() And DateAdd("m",-1,Date())
This works, but goes back exactly one month from the current date. If the query is run on May 16, it returns a record set from the date range April 16 to May 16. I have been asked to change it so that it would pull from the previous calendar month. As an example, it would pull everything from the month of April, whether executed on May 1 or May 16.
I have argued that it would be sufficient to have the criteria expression ask the user to input start & end dates, so that any desired range could be returned... but have been told this requires "too much input from the user" .
Can anyone help with changing the expression to meet their demand?
You could add a column in your query that returns just the month number, format([YourDate],"m") and have this as the first item in your GroupBy and it will sort by the calendar month. Just don't have the actual date in the GroupBy so you don't show each day.