Criteria to exclude all but this quarter

[LMB]

Hi,

I am using Access 2000. In a query, I have a date field with this criteria.
It shows only the dates for the last 12 weeks.

=DateAdd("d",-84,Date())

Is there a way to do the same thing but I need the query to only show
records for each quarter. The dates entered are for multiple years because
I do need to keep track of the overall records as well. The quarters are
Jan-March, April-June, July-Sept, Oct-Dec.

I am thinking I may need 2 queries? One to define the quarters, then one to
filter the dates or maybe I just need an expression in this query to define
the quarters...anyway, I am getting mixed up and need some help.

Thanks,
Linda

 

[Guest]

Linda:

The DatePart function can return the quarter for any date value, so to
return all rows for the current quarter the query would go like this:

SELECT *
FROM YourTable
WHERE YEAR(YourDateField) = YEAR(DATE())
AND DATEPART("q",YourDateField) = DATEPART("q",DATE());

Ken Sheridan
Stafford, England

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you mean have the 4 quarters across the top of the query? That'd be
a cross-tab query.

PARAMETERS [Beginning Date] Date, [Ending Date] Date;
TRANSFORM SUM(Sales) As theValue
SELECT SalesDistrict, Sum(Sales) As Total
FROM Sales
WHERE SalesDate Between [Beginning Date] And [Ending Date]
GROUP BY SalesDistrict
PIVOT Year(SalesDate) & "Q" & DatePart("Q",SalesDate)

If the parameters were 1/1/2005 to 12/31/2005, the results would be:

SalesDistrict Total 2005Q1 2005Q2 2005Q3 2005Q4
============= ===== ====== ====== ====== ======
Northwest 25 5 5 5 10
Southeast 100 25 25 25 25
..... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDf/QIechKqOuFEgEQJb1QCgx99V8WqrimMbWkMz/4wIbKtUlw0AoPJU
FJdvij9+JGs/MUoz94EU0Nuw
=MLmQ
-----END PGP SIGNATURE-----

 

[LMB]

Thanks, Ken,

I think I would use SQL for that? At this point, I am still using the query
grid to create my queries. I'm going to try to fill in the tbl and field
names into this in the SQL view and see what the query grid would look like.
I probably won't get it done until tomorrow evening.

Thanks for answering. England?! My daughter will be so excited, she is a
huge Oasis fan (sorry I know that was really off topic)

Linda