Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012

    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?

  2. #2
    Join Date
    Jan 2005
    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.

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 15
    You could use:
    BETWEEN DateSerial(Year(DateAdd("m", -1, Now)), Month(Now) - 1, 1) AND DateSerial(Year(DateAdd("m", -1, Now)), Month(Now), 1 - 1)
    Have a nice day!

Posting Permissions

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