Selecting sales data based on dates and duration in form

[Tlewman]

I am creating a db to track the sales made by our reps to justify additional headcount. I receive sales data from our retailer via software that downloads my file (in access format) into a specified location.
The table is called tblDailySales and contains all sales by day for the past two years. It has the following fields:
Item as Nbr
Str as Nbr
Day as Text
Qty as Nbr

I have created a form for the reps to log the actual amount sold to each store/area. The underlying table (tblEventSales) contains the following fields:
Event type as Text (promo, weather related, etc)
Item as Nbr
Str as Nbr
Start Date as Text
Duration as Nbr (how many days they will sell)
Qty sold as Nbr

Now I need to determine what the average sales from tblDailySales have been for the past four weeks based on the Start Date listed in tblEventSales. Ex: If the start date for the sale is 11/15/2004, I need to average the sales from 10/26 through 11/14.

Then, I need to determine what the total sales are in tblDailySales beginning with the Start date listed in tblEventSales through the duration (# days listed in tblEventSales). Ex: if the start date for the sale is 11/15/2004, and the duration is 10 days, what were the actual sales from tblDailySales starting on 11/15 through 11/24. (Obviously this will run once the duration is complete).

I have joined my tables on the store and item numbers. Once I get the average sales amount and the totals sold, I can figure incremental and sell thru.

How do I get this date thing to work?????

 

[Tlewman]

OK it was a formatting issue. I put a formula in the StartDate, EndDate and Daily fields to format them all the same and it is now working.
Thanks for all your help!