Help with Calculation

  • Thread starter Thread starter lrowley
  • Start date Start date
L

lrowley

I have 3 fields; admissiondate, transferdate, dischargedate. The
admissiondate will always have data, depending on whether it was a transfer
or discharge one of these will be blank. I want to return total of days
stayed if what was a transfer or discharge

This is the statement I tried but it just returns -1

=IIf([dischargedate] Is Not Null,[dischargedate]-[admitdate]) Or IIf(
[transferdate] Is Not Null,[transferdate]-[admitdate])
 
SELECT tblDates.DischargeDate, tblDates.AdmissionDate,
tblDates.TransferDate,
DateDiff("d",[AdmissionDate],IIf(IsNull([TransferDate]),iif(isnull([DischargeDate]),AdmissionDate,DischargeDate),[TransferDate]))
AS DifferenceDate
FROM tblDates;

or
=DateDiff("d",[AdmissionDate],IIf(IsNull([TransferDate]),iif(isnull([DischargeDate]),AdmissionDate,DischargeDate),[TransferDate]))
 
Why not add an option group for Transfer and Discharge, delete TransferDate
and DischargeDate and add ExitDate. Then total days stayed equals:
DateDiff(["d",[AdmissionDate],[ExitDate])

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
I have 3 fields; admissiondate, transferdate, dischargedate. The
admissiondate will always have data, depending on whether it was a transfer
or discharge one of these will be blank. I want to return total of days
stayed if what was a transfer or discharge

This is the statement I tried but it just returns -1

=IIf([dischargedate] Is Not Null,[dischargedate]-[admitdate]) Or IIf(
[transferdate] Is Not Null,[transferdate]-[admitdate])
Click to expand...

Simplest would be to use the NZ function:

DateDiff("d", [admitdate], NZ([dischargedate], [transferdate]))

John W. Vinson [MVP]
 
Thanks your second suggestion worked perfectly.

B. Edwards said:
SELECT tblDates.DischargeDate, tblDates.AdmissionDate,
tblDates.TransferDate,
DateDiff("d",[AdmissionDate],IIf(IsNull([TransferDate]),iif(isnull([DischargeDate]),AdmissionDate,DischargeDate),[TransferDate]))
AS DifferenceDate
FROM tblDates;

or
=DateDiff("d",[AdmissionDate],IIf(IsNull([TransferDate]),iif(isnull([DischargeDate]),AdmissionDate,DischargeDate),[TransferDate]))
I have 3 fields; admissiondate, transferdate, dischargedate. The
admissiondate will always have data, depending on whether it was a
Click to expand...
[quoted text clipped - 6 lines]
=IIf([dischargedate] Is Not Null,[dischargedate]-[admitdate]) Or IIf(
[transferdate] Is Not Null,[transferdate]-[admitdate])
Click to expand...
Click to expand...
 
Back
Top