Using an IIF Statement to calculate discharge date

  • Thread starter Thread starter ClareBear
  • Start date Start date
C

ClareBear

I am trying to write a query where I have the fields: admission date,
discharge date and I subtract the two to get hours in hospital (Total
Admission Hours: DateDiff("h",[Admit Date],[Discharge Date]), but I want an
IIF statement where by if the discharge date is blank, it uses a date that i
enter with a parameter field or the last day of the previous month. I'm not
sure I can do both inside this one calculated field or if I need to do
another one. Thanks for your help!
 
I am trying to write a query where I have the fields: admission date,
discharge date and I subtract the two to get hours in hospital (Total
Admission Hours: DateDiff("h",[Admit Date],[Discharge Date]), but I want an
IIF statement where by if the discharge date is blank, it uses a date that i
enter with a parameter field or the last day of the previous month. I'm not
sure I can do both inside this one calculated field or if I need to do
another one. Thanks for your help!
Click to expand...

DateDiff("h", [Admit Date], NZ([Discharge Date], [Your Parameter Here]))

or

DateDiff("h", [Admit Date], NZ([Discharge Date], DateSerial(Year(Date()),
Month(Date()), 0))

respectively.
 
Try this --
Total Admission Hours: DateDiff("h",[Admit Date],IIF([Discharge Date] Is Not
Null, [Discharge Date], IIF([parameter field] Is Not Null, [parameter field],
DateSerial(Year(Date()), Month(Date()), 0))))
 
Thank You...Both worked. I think i'm going to need to try and add to this by
using only the current months hours, so if they were admitted in the previous
month, i still only want the total from the beginning of the current month.
For instance, admitted 07/12/09, I am running the query for the month of
August, so I would only want from 08/01/09 through 08/30/09, but if they were
admitted on 08/12/09, it still would only want from 08/12/09. Not sure if
this is possible, I could just have the analyst subtract out the previous
month's hours manually, but if it is possible...WOW!! Any suggestions???
Thanks again...
--
cb


KARL DEWEY said:
Try this --
Total Admission Hours: DateDiff("h",[Admit Date],IIF([Discharge Date] Is Not
Null, [Discharge Date], IIF([parameter field] Is Not Null, [parameter field],
DateSerial(Year(Date()), Month(Date()), 0))))

--
Build a little, test a little.


ClareBear said:
I am trying to write a query where I have the fields: admission date,
discharge date and I subtract the two to get hours in hospital (Total
Admission Hours: DateDiff("h",[Admit Date],[Discharge Date]), but I want an
IIF statement where by if the discharge date is blank, it uses a date that i
enter with a parameter field or the last day of the previous month. I'm not
sure I can do both inside this one calculated field or if I need to do
another one. Thanks for your help!
Click to expand...
Click to expand...
 
Try this --
Last Months Admission Hours: DateDiff("h",IIf([Admit
Date]<DateSerial(Year(Date()),Month(Date())-1,1),DateSerial(Year(Date()),Month(Date())-1,1),[Admit
Date]),IIf([Discharge Date] Is Not Null,[Discharge
Date],DateSerial(Year(Date()),Month(Date()),0)))
--
Build a little, test a little.


ClareBear said:
Thank You...Both worked. I think i'm going to need to try and add to this by
using only the current months hours, so if they were admitted in the previous
month, i still only want the total from the beginning of the current month.
For instance, admitted 07/12/09, I am running the query for the month of
August, so I would only want from 08/01/09 through 08/30/09, but if they were
admitted on 08/12/09, it still would only want from 08/12/09. Not sure if
this is possible, I could just have the analyst subtract out the previous
month's hours manually, but if it is possible...WOW!! Any suggestions???
Thanks again...
--
cb


KARL DEWEY said:
Try this --
Total Admission Hours: DateDiff("h",[Admit Date],IIF([Discharge Date] Is Not
Null, [Discharge Date], IIF([parameter field] Is Not Null, [parameter field],
DateSerial(Year(Date()), Month(Date()), 0))))

--
Build a little, test a little.


ClareBear said:
I am trying to write a query where I have the fields: admission date,
discharge date and I subtract the two to get hours in hospital (Total
Admission Hours: DateDiff("h",[Admit Date],[Discharge Date]), but I want an
IIF statement where by if the discharge date is blank, it uses a date that i
enter with a parameter field or the last day of the previous month. I'm not
sure I can do both inside this one calculated field or if I need to do
another one. Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top