Last 12 Full months + Month to Date

[FrostByte]

Hi,

I'm trying to get a formula in my query that full pull the last 12 full months + month to date.

I've been using...
DateAdd("m",-12,Date()) And Date()

But this doesn't give a full month for April 2020 (which is the 12 month cut off)


Cheers

 

[Ranman256]

don't build formulas, use a form with date fields: txtStartDate, txtEndDate.
then the sql is : select * from table where [datefield] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

user can manually enter dates in the 2 boxes.

you can add to the form ,quick picks of date ranges (photo)
some buttons to quick fill the 2 date text boxes.

 

[FrostByte]

don't build formulas, use a form with date fields: txtStartDate, txtEndDate.
then the sql is : select * from table where [datefield] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

user can manually enter dates in the 2 boxes.

you can add to the form ,quick picks of date ranges (photo)
some buttons to quick fill the 2 date text boxes.

Its for a dashboard designed to show the last 12months with no interaction.

 

[Gasman]

Try
dateadd("m",-12,DateSerial(Year(Date),Month(Date),0))

 

[plog]

'm trying to get a formula in my query that full pull the last 12 full months + month to date.

Just to be clear, what you wrote means that your data should show records from 4/4/2020 as well as records from 4/30/2021. Correct?

If so this is the criteria:

>=CDate(Month(Date()) & "/1/" & Year(Date())-1) And <CDate(Month(DateAdd("m",1,Date())) & "/1/" & Year(DateAdd("m",1,Date())))

 

[arnelgp]

you can also try:

dateadd("yyyy",-1,dateserial(year(date()),month(date()),1)) And Date()

 

[FrostByte]

you can also try:

dateadd("yyyy",-1,dateserial(year(date()),month(date()),1)) And Date()

Tyvm,,, this is great