 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / February 2008Counting dates between 2 dates (No weekends)
I need a query (and where to place it) the counting the numbers of days between two dates (not counting weekends) How about holidays?  SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > I need a query (and where to place it) the counting the numbers of days > between two dates (not counting weekends) No Holidays SignatureSandra > How about holidays? > > > I need a query (and where to place it) the counting the numbers of days > > between two dates (not counting weekends) If the two dates are without time (ie, time is 00:00:00), then 1+ date2 - date1 give the number of days between the two dates (you may remove the +1 if you want exclusive rather than inclusive). You could also used: 1 + DateDiff("d", earliestDate, latestDate). Next, to get the number of Sunday, between two dates, none of the date being itself a Sunday, you can use: DateDiff("ww", earliestDate, latestDate, vbSunday) and to get the number of Saturday: DateDiff("ww", earliestDate, latestDate, vbSaturday) So, in a query, in the query designer, in the grid, add the computed expression: 1 + DateDiff("d", earliestDate, latestDate) -DateDiff("ww", earliestDate, latestDate, 1)-DateDiff("ww", earliestDate, latestDate, 7) That works only if none of the date is a Saturday, neither a Sunday. Hoping it may help, Vanderghast, Access MVP >I need a query (and where to place it) the counting the numbers of days > between two dates (not counting weekends) When I do this I am getting a compile error? This is what I am using Total days: 1+DateDiff("d",[Date Issued],[Signature Date])-DateDiff("ww",[Date Issued],[Signature Date],1)-DateDiff("ww",[Date Issued],[lSignature Date],7) SignatureSandra > I need a query (and where to place it) the counting the numbers of days > between two dates (not counting weekends) There is a [lSignature Date], should be [Signature Date]. Check the References... (In the VBE, under the menu Tools) Is there a checked reference which starts its description with the word "MISSING" ? If so, uncheck it, or fix it. Hoping it may help, Vanderghast, Access MVP > When I do this I am getting a compile error? > [quoted text clipped - 6 lines] >> I need a query (and where to place it) the counting the numbers of days >> between two dates (not counting weekends) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.