Date Function

[rollover99 via AccessMonster.com]

I am running a query from a table that limits the output of data to a
specific time frame. I am working on the last month minus 12 months. So
what I want to see is from April of this year to May of last year. This is
progressive as the months change. i have been using:

Between (DateAdd("m",-1,Date())) And (DateAdd("m",-12,Date()))

This gives me this years May which I do not want.

Any help would be appreciated

 

[KARL DEWEY]

Try this ---
Between DateAdd("yyyy",-1,Date()-Day(Date())+1) And Date()-Day(Date())
This subtract the day of the month from today's date resulting in the last
day of the previous month for the ending date. It adds one day and then
subtracts one year to get the beginning date.

 

[John W. Vinson]

I am running a query from a table that limits the output of data to a
specific time frame. I am working on the last month minus 12 months. So
what I want to see is from April of this year to May of last year. This is
progressive as the months change. i have been using:

Between (DateAdd("m",-1,Date())) And (DateAdd("m",-12,Date()))

This gives me this years May which I do not want.

Any help would be appreciated

= DateSerial(Year(Date()) - 1, Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

run today will return all records between May 1, 2006 and the end of the day
April 30, 2007.

John W. Vinson [MVP]

 

[John Spencer]

You might want to try the following.

Between DateSerial(Year(date()), Month(Date())-12,1) And
DateSerial(Year(Date()), Month(Date()),0)

That translates to
May 1, 2006 to April 30, 2007

The trick is that the zero day of a month is the day previous to the first
of the month. The day previous to the first of a month = the last day of
the previous month.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michael Gramelspacher]

I am running a query from a table that limits the output of data to a
specific time frame. I am working on the last month minus 12 months. So
what I want to see is from April of this year to May of last year. This is
progressive as the months change. i have been using:

Between (DateAdd("m",-1,Date())) And (DateAdd("m",-12,Date()))

This gives me this years May which I do not want.

Any help would be appreciated

Yet another idea.

Use query with Northwind:

SELECT Orders.*
FROM Orders
WHERE OrderDate
BETWEEN DATEADD("m",DATEDIFF("m",2,#1998-05-09#) - 12, 2)
AND (DATEADD("m",DATEDIFF("m",2,#1998-05-09#),2) - 1);

Find whole months between #1/1/1990# (base date 2) and current
month and substract 12 months, and add this to #1/1/1990#.
Adding whole months to first day of month base date gives us
first day of month 12 month ago.

Find whole months between #1/1/1990# (base date 2) and current
month, add this to #1/1/1990# to get 1st day of current month.
Substract 1 day to get last day of previous month.

Replace #1998-05-09# with Now, if working with current data.

 

[rollover99 via AccessMonster.com]

= DateSerial(Year(Date()) - 1, Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

run today will return all records between May 1, 2006 and the end of the day
April 30, 2007.

This returned no data...

 

[rollover99 via AccessMonster.com]

You might want to try the following.

Between DateSerial(Year(date()), Month(Date())-12,1) And
DateSerial(Year(Date()), Month(Date()),0)

That translates to
May 1, 2006 to April 30, 2007

This only returns this year.

 

[rollover99 via AccessMonster.com]

You might want to try the following.

Between DateSerial(Year(date()), Month(Date())-12,1) And
DateSerial(Year(Date()), Month(Date()),0)

Scratch my last this worked. Thanks John...