in between date function

[joe789]

Hi Folks,

I know this is possible, just not sure how to proceed or what function to use ... I have a normalized database of unique identifiers, admit data, and discharge date. I am attempted to count only the dates within a certain range between admit and discharge dates for each record. For example, I would like to compute the number of days someone was in admission between #07/01/03# and #06/30/04# only, not counting any days outside of that range.

So if a record has an admit date of 5/3/00 and discharge date of 12/7/03, the value returned would be 159 days, because that is the number of days between #07/01/03# and #06/30/04# the record was enrolled. If a records admit date is 01/01/01 and discharge is 07/26/05, the value returned would be 365 days.

Any ideas?

Thanks,

Joe

 

[Len Boorman]

Think you can simply do it within a query'

have the query select entry and discharge dates and then set up a claculated field.

Duration:[Discharge date]-[Entry Date]

This should give you elapsed days

L

 

[mresann]

I would go one step further...

Duration: (Iif([DischargeDate] > #06/30/04#, #06/30/04#, [DischargeDate]) - (Iif([EntryDate] < #07/01/03#, #07/01/03#, [EntryDate]))