Diff2Dates

[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!!

 

[Daniel Pineault]

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.

 

[Mr. B]

For sorting purposes you may need to just return the value as all minutes so
you can do the comparison and sort on the number.

datediff("n",date1,date2)

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Douglas J. Steele]

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

 

[John Spencer]

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

 

[youkr]

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.

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!!

.