Calculating Days (Access 97)

Topic

New Reply

If I have an “ContactId” field, an “AdmissionDate” field and an “DepartureDate” field, with dates input throughout this century, how do I get a total of days only between 01/01/1998 and 31/12/2002 to be added? I.e. if I had a patient with an Admission date of 01/01/1997 and a departure date of 31/12/2001 I’d like it to calculate between 01/01/1998 and 31/12/2001 etc.

Thanks.

Reply | Quote

Replies

You can use expressions with IIf to do this:

IIf([DepartureDate]>#31/12/2002#,#31/12/2002#,[DepartureDate])-IIf([AdmissionDate]<#01/01/1998#,#01/01/1998#,[AdmissionDate])

(assuming a system date setting dd/mm/yyyy)

Reply | Quote

You can use expressions with IIf to do this:

IIf([DepartureDate]>#31/12/2002#,#31/12/2002#,[DepartureDate])-IIf([AdmissionDate]<#01/01/1998#,#01/01/1998#,[AdmissionDate])

(assuming a system date setting dd/mm/yyyy)

Reply | Quote