Average Weeks

[royalrochelle]

I work at a research center and I am trying to get the average weeks that a patient was on a study.

If the patient is no longer on the study I do the following:
WeeksOnStudy = (Off_Study - On_Study) / 7

but if the patient is still on the study I do the following:
WeeksOnStudy = (Date - On_Study) / 7

So the code I use to get the results I want is:

If Not IsNull(Off_Study) Then
WeeksOnStudy = (Off_Study - On_Study) /7
Else
WeeksOnStudy = (Date - On_Study) / 7
End If

How would I get the Average weeks the patient was onstudy? It is tricky because WeeksOnStudy = (Date - On_Study) / 7 does not come from a table and is created through my calculations.

 

[pbaldy]

This would probably be simpler to handle both:

WeeksOnStudy = (Nz(Off_Study, Date()) - On_Study) /7

 

[pbaldy]

Oh, and you should still be able to average a calculated field in a query.

 

[royalrochelle]

Thank you so much. This worked!