I already know how the DateDiff function works, to return days, hours, mins, seconds or any givin variant, but my problem is making one field show the days, hours, mins and seconds all as one value and still being able to perform domain aggregate functions on them.
No, I don't. I appologize if I offended you. Many people do post asking questions without first checking the help file or other readily available resources.
I was answering the question:
Originally Posted by mc_tuppz
I use a date differentiation and return the hours, mins and seconds....but HOW do i return the days as well?
For your more clarified question in the second post, to combine days, minutes, and seconds into one field you have several options, depending on how your domain aggregate functions are setup.
The easiest to work with would be a number result. Since the return value is a Long, you don't get decimals. So, the only way to be close to accurate using numbers is to return the value in seconds and then convert to days, hours and minutes. This would easiest be done with a custom function in code:
Public Function fnDateDiff(dt1 As Date, dt2 As Date) As String
' There are many ways to do this, here is an example of one.
' USe varients of this functions to strip portions of the date difference
Dim d As Long
Dim h As Long
Dim m As Long
Dim s As Long
s = DateDiff("s", dt1, dt2) ' the total number of seconds between the dates
m = Fix(s / 60) ' the number of whole minutes
s = s Mod 60 ' the number of remaining seconds
h = Fix(m / 60) ' the number of whole hours
m = m Mod 60 ' the number of remaining minutes
d = Fix(h / 24) ' the number of whole days
h = h Mod 24 ' the number of remaining hours
fnDateDiff = d & "D " & h & "H " & m & "M " & s & "S"
Similarly, you can use the MOD operator to get just each portion, like so:
For Seconds only, use: =DateDiff("s",[dt1],[dt2]) Mod 60
For Minutes only, use: =DateDiff("m",[dt1],[dt2]) Mod 60
For Hours only, use: =DateDiff("h",[dt1],[dt2]) Mod 24
and for days, use: =DateDiff("d",[dt1],[dt2])
To put it together into one number being the decimal number of days, use:
Another option would be to leave the values as dates. Domain functions will work with dates (to the software, a date is just a formatted number anyway). Unfortunately, Access doesn't let you operate directly on dates the same way Excel does, so this would require extra steps.
Many ways to do this, these are just some examples.