Selecting Relative Months in MS Access Query

[jayw0e]

Hi, I have a table containing ledger records upon which I wish to build a query. One of the fields in this table contains dates. I would like my query to select records from the table where the date falls within eitther the previous month, the current month, or next month. So, posting this in December, my query would bring back all records where the date value was between 01/11/2015 and 31/01/2016. And when the same query runs next month (January), it will return records between 01/12/2015 and 29/02/2016.

Probably a really easy one, just not something I've done before in MSAccess. Any help gratefully received.

[jayw0e]

Sorry, I should specify that I am using UK date format in my example, ie dd/mm/yyyy

[InsuranceGuy]

Give this a try?

Between dateadd("m",-1,dateserial(year(now()),month(now()),1) and dateadd("m",2,dateserial(year(now()),month(now()),1)-1

Cheers,

Jeff

[June7]

Review http://allenbrowne.com/ser-36.html

[jayw0e]

Thank Jeff, made a minor amendment thus: Between DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1)) And DateAdd("m",2,DateSerial(Year(Now()),Month(Now()), 1))-1

[InsuranceGuy]

Good catch, sorry for the freehand/unverified code. Cheers!