Date math in query - strange results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My query returns
OpenDate, CloseDate, DaysOpe

wit
iif(NZ(T.CloseDate,"")="",Now() - T.OpenDate,T.CloseDate - T.OpenDate) AS DaysOpe

It seems to work when there is no CloseDate but when it subtracts OpenDate from Close Date, I get a negative number with an exponent. I need the result in days, i.e. .5 for half a day. How do I do this? Any help appreciated.
 
Try

IIF(T.CloseDate Is Null,
DateDiff("h",T.Opendate,Now())/24,
DateDiff("h",T.OpenDate,T.CloseDate)/24)

DateDiff("h",Date1,Date2) will return the number of hour boundaries crossed
between any two date/times. So, it may not be quite as accurate as you want
since the number of hour boundaries between 10:59 AM and 11:01 AM is one even
though only two minutes have elapsed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query & calculated field 1
Union query error 3
How do I have a listbox in a form update a query 2
Help with small result type 2
Date Format in a Query 5
Calculated Field in Query - Date Format 5
DSum update query with multiple fields 0
Formatting date gives bad results in query 2

Back
Top