If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Diff2Dates
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!! |
#2
|
|||
|
|||
Diff2Dates
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!! |
#3
|
|||
|
|||
Diff2Dates
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!! |
#4
|
|||
|
|||
Diff2Dates
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" wrote in message ... 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!! |
#5
|
|||
|
|||
Diff2Dates
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!! |
#6
|
|||
|
|||
Diff2Dates
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" wrote: 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!! . |
Thread Tools | |
Display Modes | |
|
|