Counting based on date

[MadCatmk2]

Hiya

Crystal 9
Access 97 Database

I have a question regarding a report i am creating. I'll keep it as simple as i can in the hope that someone will have some idea as to what i need to do.

The report is to show the dependency of a patient and bases this on the the amount of visits that the patient has in a seven day period.
For each date on the report i want to check each seven day period around the given date and count the number of visits made within the time frame.

i.e.
If the given date is 29/12/2003 i want to count the contacts that are on firstly done between
23/12/2003 and 29/12/2003
then
24/12/2003 and 30/12/2003
...
up until the last seven day period
29/12/2003 and 4/1/2004

The greatest number of visits will always be stored.

My problem is that i am not quite sure as to how i can count these visits in a formula.

Hopefully someone can help me out as to how to start of this.

Thanks in advance.

 

[Madawc]

In Crystal 8.5, you could do it by formulas. Make

{given_date}-6

Do a set of formulas, adding 7 days to the previous formula. Crystal adds or subtracts days by default, though you can do other additions and subtractions using DateAdd

Madawc Williams (East Anglia)

 

[MadCatmk2]

Thanks for tne reply. I probably could have made more sense when posting the original question. I know that i can use the dateadd function to get previous and future dates. The problem i have is how to go about counting the visits which correspond to these dates and then loop through each of the 7 day periods (Although i figure i can use a for loop for that part). Its really the count part i'm having problems with, maybe i am missing something very fundamental. Would it be best to use a sub report with the selected date passed through to it?

Thanks once again

 

[MadCatmk2]

I have something which i thought may work in this instance but there seems to be a problem with the IF statement in the FOR loop. The code i have is as follows:

global numbervar array visit_count := [0,0,0,0,0,0,0];
numbervar i;
numbervar j;


j := 0;
for i := -6 to 0 step 1 do
(

    if {CONTACTS.DATE_OF_VISIT} >= dateadd("d", i, {?Pm-CONTACTS.DATE_OF_VISIT}) and {CONTACTS.DATE_OF_VISIT} <= dateadd("d", j, {?Pm-CONTACTS.DATE_OF_VISIT}) then
    (
    visit_count[i+7] := visit_count[i+7] + 1
    )
    else
    visit_count[i+7] := visit_count[i+7] + 0;

  j := j + 1;

);

I've checked to see that the for loop processes the correct date ranges and it does seem to. I need a way to add up all the dates that are true to the IF statement and then place that value in the correct array location. Once i have the total visits for each seven day period then i will extract the maximum value from the array and use this to calculate dependency. Does anyone have any ideas how i can get the IF statement to do this?

Hope that made sense.

Thanks in advance.