Need to calcuate fields between dates

[choe]

I have a database that I need to calculate a query that is based on 3 dates. For example...I have an admission date and a discharge date in my database. If there is no date in the discharge date then I need it to calculate the number of days between the admission date and "today's date." If there is a discharge date then I need to calculate the difference between admission date and discharge date. Basically i need a length of stay figure. I can figure the formula in excel but cannot get it to work in access. Here is my formula in excel =If(ISBLANK(DischargeDate),Sum(today()),sum(Discha rgeDate-AdmissionDate). Can someone help me?

[ssanfu]

Need to use DateDiff() function.

Try

DateDiff("d",[AdmissionDate],Nz([DischargeDate],Date()))

[ranman256]

I would use a UNION query

SELECT [admission date],[DISCHARGE date],DATEDIFF("D", [admission date],DATE()) AS DAYS WHERE [discharge date] IS NULL
UNION
SELECT [admission date],[DISCHARGE date],DATEDIFF("D", [admission date],[DISCHARGE date]) AS DAYS WHERE [discharge date] IS NOT NULL