Access Date Criteria

[afa]

I would like to run a query with the [SalesDate] field to contain dates from the current month the report is running Date() to one year from now AND include all dates within the last month.

For example, if I run it on 9/7/16, it will output dates from 9/1/16 to 9/30/17.
I tried
Between Month(Date()) And Month(Date()+365) ...does not work.

Month(Date()) is successful in retrieving all dates within the current month the query is run but I cannot get all the dates within the last month.

[aytee111]

There must be an easier way than this!

First day of month: CDate(Format(Date(),"mm") & "/01/" & Format(Date(),"yyyy"))
Last day of month: DateAdd("m",1,[fdom])-1
Next year: [ldom] + 365

Put it all together in a "Between" statement

[ssanfu]

Try:

....WHERE [SalesDate] Between DateSerial(Year(Date()), Month(Date()),1) AND DateSerial(Year(Date())+1, Month(Date())+1,0)

[aytee111]

Brilliant!

[afa]

Yup that works perfect.