Range of Dates based on Quarter

[Larry G.]

I have a table that has several sampling events. What I need to do is use the
data only for the most recent 16 quarters (This quarter as well as the
previous 15). Each sampling event can take place at any date within the
quarter. But I just need the 16.
Using a regular query, this is the data I get (this is just the last 16
entries). I know that I am going to have to use Date and my "Date Sample
Collected" field, just not sure how to go about writing the query.

Date Sample
Collected Well ID 15 1,2-Dichloroethane (DCA)
10/17/2001 OW-69 < 5.00
3/21/2002 OW-69 < 0.50
10/14/2002 OW-69 < 5.00
12/19/2002 OW-69 < 5.00
3/29/2003 OW-69 < 0.50
6/25/2003 OW-69 < 5.00
9/29/2003 OW-69 < 5.00
2/13/2004 OW-69 < 0.50
7/28/2004 OW-69 < 5.00
9/25/2004 OW-69 < 5.00
1/15/2005 OW-69 < 5.00
4/15/2005 OW-69 < 0.50
6/2/2005 OW-69 < 0.50
8/10/2005 OW-69 < 5.00
11/10/2005 OW-69 < 5.00
2/10/2006 OW-69 < 0.50

There is more data but this is all I am interested in getting

 

[Larry G.]

I got it! ALL by myself!~

If anyone needs this in the future, here is what I did

Sample Date between Now()-1640 and Now()

 

[John Vinson]

I got it! ALL by myself!~

If anyone needs this in the future, here is what I did

Sample Date between Now()-1640 and Now()

Well... that's not quite right. That will get the past 1640 days; it
will only get the last 16 quarters in the odd chance that you run the
query on the last day of the quarter! Otherwise the records will start
sometime in the midst of a quarter.

You'll need a fairly complicated DateSerial expression since quarters
aren't a standard date division like months. Assuming that the
quarters begin 1/1, 4/1, 7/1 and 10/1, try

BETWEEN DateSerial(Year(Date()) - 4, 4*(Month(Date()\4) - 1, 1) AND
Date()

Note that Now() does not return the date - it returns the current date
and time, accurate to the second. It's usually best to use Date()
rather than Now() unless you need the time portion.

John W. Vinson[MVP]

 

[Larry G.]

John I copied and pasted your code at the bottom, I am being told it is
missing a closing parenthesis, bracket, or vertical bar. It doesn't look like
it to me, can you look at it again?

Thanks!

 

[John Vinson]

John I copied and pasted your code at the bottom, I am being told it is
missing a closing parenthesis, bracket, or vertical bar. It doesn't look like
it to me, can you look at it again?

Hm. Should be

BETWEEN DateSerial(Year(Date()) - 4, 4*(Month(Date())\4) - 1, 1) AND
Date()

Was missing a close paren after Month(Date().

John W. Vinson[MVP]