Hi guys,

It's my first post and to be honest, I'm not very good with Access queries. I've tried to find tutorials that step beyond SELECT * From table where something='something' but I can never find the right material, hence why I'm here hoping that someone can help me http://www.access-programmers.co.uk/ubb/smile.gif

I've got 2 problems. I'll explain after I show you my query (Access 2000 btw.):

SELECT Count(Import.ImportID) AS Week1
FROM Import
WHERE (((Import.HitDate)>=#1/10/2001# And (Import.HitDate)<#8/10/2001#));

This counts the number of times all dates from 1/09/01 to 7/09/01 appear in the selected fields. That's ok. However, I want to be able to do this for all 5 weeks in a month. I've tried using the following to get 2 results, one for each week, but it doesn't work:

SELECT Count(Import.ImportID) AS Week1
FROM Import
WHERE (((Import.HitDate)>=#1/10/2001# And (Import.HitDate)<#8/10/2001#))

Union SELECT Count(Import.ImportID) AS Week2
FROM Import
WHERE (((Import.HitDate)>=#8/10/2001# And (Import.HitDate)<#15/10/2001#));

This seems to add to the first count and not give me individual results.

I eventually want to end up with 5 different results of which I can then add up the values retrieved.

For example, if after I retrieved the 5 values from the query (which I can't do at the moment.) I got the following values, 1,2,3,4 and 5, then I'd be able to add them up within this same query to get a total of 15.

Sorry for the long post but I'm really stuck at the moment. Could anyone help me with this or point me in the right direction?

Thanks for your time http://www.access-programmers.co.uk/ubb/smile.gif

Lizard.

Create a form that has two visible and two hidden fields. The visible fields will be for the user to enter the StartDate and EndDate and the invisible fields will be to hold each date after reformatting into US date format. Change your query's where clause to reference the hidden date fields on the form.

SELECT Import.HitDate
FROM Import
WHERE Import.HitDate Between Forms!YourFormName!HiddenStartDate And Forms!YourFormName!HiddenEndDate;

Then use the report wizard to build a report that groups by week. Add a count field to the week footer and suppress printing the detail.

Thanks for taking the time to reply Pat. That worked a treat, thankyou very much http://www.access-programmers.co.uk/ubb/smile.gif

Regards,

Lizard.