By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

cross tab queries, Access, and date criteria

P: 25
Im creating a cross tab query which sorts via [SteveCode] and sums the [Tonnage] for each type of [RecyType]. I want to be able to add a date criteria, so the user can specify that the query processes data between two dates.

I have looked long and hard on the net for any answers and it seems that this has appeared often but without a proper solution. I offer the following SQL of my query:

Code: ( text )
PARAMETERS [Forms]![Recycling Rates]![From Date] DateTime, [Forms]![Recycling Rates]![To Date] DateTime;
TRANSFORM Sum(Tonnage.Tonnage) AS SumOfTonnage
SELECT Tonnage.SteveCode
FROM Tonnage
WHERE (((Tonnage.SteveCode) Is Not Null) AND ((Tonnage.RecyType)="Dry (Recyclable)" Or (Tonnage.RecyType)="Green (Recyclable)" Or (Tonnage.RecyType)="Glass (Recyclable)" Or (Tonnage.RecyType)="Food (Recyclable)" Or (Tonnage.RecyType)="Residual") AND ((Tonnage.Date) Between [Forms]![Recycling Rates]![From Date] And [Forms]![Recycling Rates]![To Date]))
GROUP BY Tonnage.SteveCode
PIVOT Tonnage.RecyType;

If i change the two date parameters, the same results will show so it obviously is not working.

Any ideas?
Sep 15 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.