Last 12 Full months + Month to Date (1 Viewer)

FrostByte

Registered User.
Local time
Today, 15:27
Joined
Jan 15, 2015
Messages
56
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

Well-known member
Local time
Today, 11:27
Joined
Apr 9, 2015
Messages
4,339
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.
 

Attachments

  • date range form.png
    date range form.png
    15.1 KB · Views: 210

FrostByte

Registered User.
Local time
Today, 15:27
Joined
Jan 15, 2015
Messages
56
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

Enthusiastic Amateur
Local time
Today, 15:27
Joined
Sep 21, 2011
Messages
14,044
Try
dateadd("m",-12,DateSerial(Year(Date),Month(Date),0))
 

plog

Banishment Pending
Local time
Today, 10:27
Joined
May 11, 2011
Messages
11,611
'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:

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:27
Joined
May 7, 2009
Messages
19,169
you can also try:

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

Users who are viewing this thread

Top Bottom