A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Diff2Dates



 
 
Thread Tools Display Modes
  #1  
Old March 17th, 2010, 12:06 AM posted to microsoft.public.access
youkr
external usenet poster
 
Posts: 10
Default 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  
Old March 17th, 2010, 01:01 AM posted to microsoft.public.access
Daniel Pineault
external usenet poster
 
Posts: 658
Default 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  
Old March 17th, 2010, 01:06 AM posted to microsoft.public.access
Mr. B[_4_]
external usenet poster
 
Posts: 171
Default 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  
Old March 17th, 2010, 02:42 AM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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  
Old March 17th, 2010, 01:48 PM posted to microsoft.public.access
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old March 18th, 2010, 04:38 AM posted to microsoft.public.access
youkr
external usenet poster
 
Posts: 10
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:16 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.