I am using the DateDiff function, but I would like for it to give me 3 decimal places. How should my query be altered to achieve such result? -- I need this done via the query itself not a VBA function.
Date123: DateDiff('d', [startdate], [enddate])
1 Answer
For a line that you can just put into a query, I'd use something like the following.
Format(DateDiff("s",[DateOne],[DateTwo])/60/60/24,"#.###")
Better practice would be to create a function in a module in your db like the following. Then call it through the query. The code is more maintainable, testable and understandable.
Public Function DateDiffInFractions(DateOne As Date, DateTwo As Date) As String
Dim SecondsDiff As Double
SecondsDiff = DateDiff("S", DateOne, DateTwo) / 24 / 60 / 60 'Days/hours/minutes/seconds
DateDiffInFractions = Format(SecondsDiff, "0.000") 'Format to 3 Decimal points. Return string
End Function
