Calculating Date

[Lamar]

I trying to create an expression in a query that will give me the "between
dates" from 7 years back to the present. Here is my problem. So when I run
the query to go seven years back I want to include all the dates within that
year. So 7 years back from today is 2/6/02, so I want all the dates for 2002
(i.e. from 1/1/02) to present). So next year on 9/12/10 I want it pull all
the dates for 2003 to the present (i.e. 1/1/03 to present).

So below is what I came up with it goes back 8 years so I make sure it
includes the entire year for the seventh year back (i.e. this query takes you
back to 2/5/01).

Between DateAdd("d",-2920,Now()) And Now()

Any help is appreciated. Thank you.

 

[KARL DEWEY]

Between DateAdd("yyyy",-7,Date()) And Date()

 

[John W. Vinson]

I trying to create an expression in a query that will give me the "between
dates" from 7 years back to the present. Here is my problem. So when I run
the query to go seven years back I want to include all the dates within that
year. So 7 years back from today is 2/6/02, so I want all the dates for 2002
(i.e. from 1/1/02) to present). So next year on 9/12/10 I want it pull all
the dates for 2003 to the present (i.e. 1/1/03 to present).

So below is what I came up with it goes back 8 years so I make sure it
includes the entire year for the seventh year back (i.e. this query takes you
back to 2/5/01).

Between DateAdd("d",-2920,Now()) And Now()

Any help is appreciated. Thank you.

The DateSerial() function can come to the rescue here:

BETWEEN DateSerial(Year(Date()) - 7, 1, 1) AND Date()

Now() does not return today's date: it returns the current date and time
accurate to the second. If your table is using Now() to fill the datefield and
you want to include the results with today's date up to the moment that the
query runs, use Now(), otherwise use Date().

 

[raskew via AccessMonster.com]

This will provide 1 January of 7 years prior to today's date:

x = dateserial(year(dateadd("yyyy", -7, date())),1,1)
? x
1/1/2002

HTH - Bob

 

[Douglas J. Steele]

<picky>

So will

x = dateserial(year(date()) - 7,1,1)

and it avoids an extra function call

</picky>

 

[raskew via AccessMonster.com]

Dear <picky>

You're right. Thanks for straightening me out.

Bob

<picky>

So will

x = dateserial(year(date()) - 7,1,1)

and it avoids an extra function call

This will provide 1 January of 7 years prior to today's date:

[quoted text clipped - 23 lines]