Query Help

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I'm having a heck of a time with this one. I need a user
to type into a form a beginning date and an ending date,
for example, 2/1/04 - 2/28/04 (I know how to do this).
The dates are not in a column in a table or query. Once
the dates are picked, I need to count the number of
records whose date in the "Date" column of my query is
less than the picked date. The picked dates need to be in
one week time periods. I've tried everything and I can't
seem to get on the right track. I appreciate any help you
can give me. Thanks.

example:

PickedDate #of Date <PickedDate
2/1/04 45
2/8/04 43
2/15/04 39
2/22/04 20
 
Alex,
You may be able to accomplish what you want with some
code behind your form. You could create an array. Can
the users enter any size date range, or is it just 1
month? You would have to size your array accordingly.
Then, you can populate the array with calculated dates.
Something like:

For i = 1 to 10
datefld = dateadd("w",i,form!fromdate)
if datefld < form!todate then
numflds = i
arrayentry(i) = datefld
else
i = 10
end if
next i

Once you have your dates, you can set up another for-next,
checking your dates against your table data.
Hope this helps.
 
A little integer math to generate a week number based on your StartDate
Parameter.

SELECT Min(PickedDate) as FirstPickedDateforWeek,
Count(PickedDate) as CountThem
WHERE PickedDate >= [StartDate] and PickedDate <=[EndDate]
GROUP By (PickedDate - [StartDate])\7 as WeekNo
ORDER BY (PickedDate - [StartDate])\7 as WeekNo

This gets the number per period, but as I re-read your statement, I see you are
looking for something a bit more complicated. I guess I will go off and ponder
 
Back
Top