Help with date formula in query

[Guest]

I am trying to calculate the number of days a case is open or was open. If
the record in the closed date column of my table is empty then I want to
calculate the number of days by the date() function, otherwise I would just
subtract with the closed date that was entered in the table for that record.
When I run the query I am receiving #Error. Thanks for your help. I've posted
my sql below.

SELECT tblTemp.[Open Date], IIf(IsNull([Closed Date]),Date()-[Open
Date],[Closed Date]-[Open Date]) AS [Days Open], tblTemp.[Ref# Number],
tblTemp.CSR, tblTemp.TL, tblTemp.Comments, tblTemp.[Closed Date],
tblTemp.Status, tblTemp.[1st or 2nd Time]
FROM tblTemp
WHERE (((tblTemp.[Open Date])>([Forms]![frmEscReportsb]![Dstart]-1) And
(tblTemp.[Open Date])<([forms]![frmEscReportsb]![DEnd]+1) And (tblTemp.[Open
Date]) Not Like "0") AND ((tblTemp.TL) Like
IIf([forms]![frmEscReportsb]![cbxTl]="Combined","*",[forms]![frmEscReportsb]![cbxTl]))
AND ((tblTemp.Status) Like
IIf([forms]![frmEscReportsb]![cbxStat]="Combined","*",[forms]![frmEscReportsb]![cbxStat]))
AND ((tblTemp.[1st or 2nd Time]) Like
IIf([forms]![frmEscReportsb]![cbxElev]="Combined","*",[forms]![frmEscReportsb]![cbxElev])));

 

[Allen Browne]

Try:
SELECT tblTemp.[Open Date],
DateDiff("d", [Open Date], Nz([Closed Date], Date())) AS [Days Open],
....

If [Closed Date] is null, the Nz() function uses Date() instead.
The DateDiff() calculates the difference in days.