Limit Access Query By Date

1
votes

I am trying to run a query in Access that would return all the records created in the current month and the previous month. Selecting the table and the fields is not the issue. In this particular query the field to limit the query is ReleaseDate and has the value of Date/Time. This query would have to take in account that the previous month may be in the previous year when the current month is January.

1
ms-access

1 Answers

0
votes

Use DateSerial() to determine the start and end of your target date range.

For example, the start of the previous month from today (4/19/2021) ...

? DateSerial(Year(Date()), Month(Date()) -1, 1)
3/1/2021

And the first of next month ...

? DateSerial(Year(Date()), Month(Date()) +1, 1)
5/1/2021

So you could apply those conditions to ReleaseDate in your query's WHERE clause like this ...

WHERE ReleaseDate >= DateSerial(Year(Date()), Month(Date()) -1, 1)
  And ReleaseDate  < DateSerial(Year(Date()), Month(Date()) +1, 1)

Note that DateSerial() will adjust the year part of its output date when you give it a month value less than 1 or greater than 12 ...

? DateSerial(2021, 0, 1)
12/1/2020