I would like to design a query in A 1

[wbishopjr]

I would like to design a query in Access that would filter my data by week. Access offers ready-made querys for day, month, quarter, and year but not week.

I already have an Excel file that does this with array formulas, but they are extremely slow on older machines and I think Access might have the spunk I need for this. I would like my results to look like this on the query:
Week Range
Begin End Quantity
5-1-02 5-7-02 55
5-8-02 5-14-02 450
... ... ...

I have played with the criteria in the query pages, but I always end up with error messages. Any ideas on how I could configure the criteria field in my query or any other way of doing this would be great!!!

Thanks, Waymond

It's amazing how much can be done when no one cares who gets the credit!

 

[RobPotts]

Hi Waymond

Try putting this in the date field you want to filter by.

Between [Please Enter Start Date] And [Please Enter End Date]

This should bring up 2 message boxes asking for the date range.

You could also use the active x calandar control. Northwinds has a good example.

Let me know if this helps
Rob

 

[wbishopjr]

Yes, that is exactly what I'm looking for. What I would like to set up though, is some way of telling the db a start date and it figure totals by week for the next year. So, you would have 52 totals in all.

Thanks!! Waymond

It's amazing how much can be done when no one cares who gets the credit!

 

[RobPotts]

Hi Waymond,

I've had a think anout this and the only method 've come up with is to create a report with 52 different calculations set up. A bit time consuming but as far as I can tell it work.

I did this by making a new report with the wizard. Adding the date field and the quantity feild. I then opened this report in design mode and deleted the page header info, and moved the detail boxes in to the report header section, and change there property to not visible.

I then added a new text box and in the control source I put the following calculation (leave [Start Date] in the calculation substiute the [Your.....] fields)

=Sum(IIf([Your date field] Between [Start Date] And DateAdd("d",6,[Start Date]),[your Quantity feild],0))

Then you need to add subsequent text boxes for each week which will look something like, changing the dateadd No for each week

Week 2
=Sum(IIf([Your date field] Between DateAdd("d",7,[Start Date]) And DateAdd("d",13,[Start Date]),[your Quantity field],0))

week 3

=Sum(IIf([Date Requested] Between DateAdd("d",14,[Start Date]) And DateAdd("d",20,[Start Date]),[Quantity],0))

When the report is opened it will ask for a start date and then will list each weeks quantity

To create titles for each week add a text box with the following calculation in the control source

week 1
=[startdate] &" to "& DateAdd("d",6,[startdate])

Subsequent weeks as before, changing the date add number
= DateAdd("d",7,[startdate])&" to " & DateAdd("d",13,[startdate])

Delete any unwanted labels
I hope this helps, let me know if this is of any use

Rob

 

[RobPotts]

P.S.

All the text boxes should be put in the report header

Rob

 

[wbishopjr]

Thanks for your help! It looks like I've finally got my answer. I really appreciate the time you've spent on this for me, this forum is great! I come away with something new and helpful every time I log in...

Cheers, Waymond

It's amazing how much can be done when no one cares who gets the credit!

 

[cheerio]

Look at the DateGrouping property available in report design view which controls how the weeks are chosen and the GroupOn property of the datetime of a particular field in the report.

Ken