Diff2Dates

  • Thread starter Thread starter youkr
  • Start date Start date
Y

youkr

I have used the above function, however was wondering if there is a way I can
change what is returned?
At times I get records 1 day 10 hours 4 minutes, and other times 1 hour 8
minutes. When I try to place these returned records in ascendingdescending
order according to this field they do not return in order as there is not a
'0' before the '01' hours. Is there any way of doing this?

The more I play aroudn the more questions I have!!
 
You can use DateDiff() but Diff2Dates() is obviously a custom function. For
anyone to help you, you'll need to post the entire function so we can see how
it works to be able to guide you.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
I assume you're talking about the function Graham Seach & I wrote
http://www.accessmvp.com/DJSteele/Diff2Dates.html

Not sure what could having a zero before the 1 would do for the example you
give (since you end up having days and hours interspersed), but you can
easily change

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
End If

If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", "
month")
End If
End If

If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If

If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", "
minute")
End If
End If

If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", "
second")
End If
End If


to

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
End If

If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
Format(lngDiffMonths, "00") & IIf(lngDiffMonths <> 1, "
months", " month")
End If
End If

If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
Format( lngDiffDays, "00") & IIf(lngDiffDays <> 1, " days", "
day")
End If
End If

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
Format(lngDiffHours, "00") & IIf(lngDiffHours <> 1, " hours", "
hour")
End If
End If

If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
Format( lngDiffMinutes, "00") & IIf(lngDiffMinutes <> 1, "
minutes", " minute")
End If
End If

If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
Format(lngDiffSeconds, "00") & IIf(lngDiffSeconds <> 1, "
seconds", " second")
End If
End If
 
If you want to sort in order, use a second call to generate the sort order,
you don't have to display this value, but you can sort by it.

DateDiff("s",[FirstDate],[SecondDate])

That should return the number of seconds between the two dates so you can sort
by that and display the results of the Diff2Dates call.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you everyone for your suggestions, I had thought by having a '0' in
front of the single digit numbers I could then just use the Sort AZ in
ascending order when needing to rearrange the data required, but the
returning a value in seconds and not displaying it would work too.
Thanks.

John Spencer said:
If you want to sort in order, use a second call to generate the sort order,
you don't have to display this value, but you can sort by it.

DateDiff("s",[FirstDate],[SecondDate])

That should return the number of seconds between the two dates so you can sort
by that and display the results of the Diff2Dates call.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have used the above function, however was wondering if there is a way I can
change what is returned?
At times I get records 1 day 10 hours 4 minutes, and other times 1 hour 8
minutes. When I try to place these returned records in ascendingdescending
order according to this field they do not return in order as there is not a
'0' before the '01' hours. Is there any way of doing this?

The more I play aroudn the more questions I have!!
Click to expand...
.
Click to expand...
 
Back
Top