Diff2Dates
in [General]
|
From: youkr on 16 Mar 2010 19:06 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!!
From: Daniel Pineault on 16 Mar 2010 20:01 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. "youkr" wrote: > 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!!
From: Mr. B on 16 Mar 2010 20:06 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 "youkr" wrote: > 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!!
From: Douglas J. Steele on 16 Mar 2010 21:42 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 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "youkr" <youkr(a)discussions.miscrosft.com> wrote in message news:C1FE7B4B-D05C-48C7-A83B-11F7A14D118F(a)microsoft.com... >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!!
From: John Spencer on 17 Mar 2010 08:48
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 wrote: > 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!! |