<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1226" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Virginia:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The only way I've found to do this is to loop
through the days in code from the first day to the last day, getting the day of
the week and incrementing a counter if it's not a Saturday or a Sunday using the
Weekday function..</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Possibly you could count the days between two dates
by building it into a summation query using 'Weekday(thedate)<>vbSaturday
and Weekday(thedate)<>vbSunday' as a criterion.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Actually, as I think about it, you should make the
whole thing a public function, passing the two dates and returning the number of
weekdays. Then you could call it from anywhere including in a
query.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>HTH</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky Smolin<BR>Beach Access Software</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=HollisVJ@pgdp.usec.com
href="mailto:HollisVJ@pgdp.usec.com">Hollis,Virginia</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">'Access Developers discussion and
problem solving'</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, September 04, 2003 4:33
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] Days Past Due -
Grace Period</DIV>
<DIV><BR></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=410272811-04092003>Ok,
I changed the query to the below code, is this the correct way to do
this?</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=410272811-04092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=410272811-04092003>PastDue:
DateDiff("d",[DateEntered],Now())-5</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=410272811-04092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=410272811-04092003>Oh!
Get this, I just got an email from mgmt, they want the days past due
to count "Buisness Days only". So it does not count weekends &
holidays!! I can't figure out how to make it count to 5 let alone skip
weekends. :-(</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=410272811-04092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=410272811-04092003>Va.</SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Hollis,Virginia
[mailto:HollisVJ@pgdp.usec.com]<BR><B>Sent:</B> Thursday, September 04, 2003
6:15 AM<BR><B>To:</B> 'Access Developers discussion and problem
solving'<BR><B>Subject:</B> RE: [AccessD] Days Past Due - Grace
Period<BR><BR></DIV></FONT>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=800320711-04092003>No, that did not work. For a project that was
entered 8/27/03, it shows 12.522 days. The old code shows it 8 days past
due, but I need it to show 3 days past due.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=800320711-04092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=800320711-04092003>I
hope someone can help me with this because I had a user complain to upper
mgmt about this, and it is causing me problems.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=800320711-04092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=800320711-04092003>Va.</SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Dan Waters
[mailto:dwaters@usinternet.com]<BR><B>Sent:</B> Wednesday, September 03,
2003 3:24 PM<BR><B>To:</B> 'Access Developers discussion and problem
solving'<BR><B>Subject:</B> RE: [AccessD] Days Past Due - Grace
Period<BR><BR></DIV></FONT>
<DIV><SPAN class=924281920-03092003><FONT face=Arial color=#0000ff
size=2>Virginia, I added some code into your example. Would
this work?</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] <B>On Behalf Of
</B>Hollis,Virginia<BR><B>Sent:</B> Wednesday, September 03, 2003 1:25
PM<BR><B>To:</B> 'accessD@databaseadvisors.com'<BR><B>Subject:</B>
[AccessD] Days Past Due - Grace Period<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=920580919-03092003>A project
review must be completed within 5 days. If a project review is over 5
days past due and the status is Pending, a label shows "Past Due &
the number of days it is past due". The Past Due date is based on the
date the record was entered into the system.</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=920580919-03092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=920580919-03092003>Problem: I
need the past due label to show not the days past due from the
DateEntered, but count the days Over 5. For example, the project was
entered 8/28/2003, once the project is >= 5 days past 8/28/2003 or on
9/4/2003 the label shows this project is past due 6 days. What I
really need is the label to show it is past due 1 day, because they
do have a 5 day grace period. I use this on forms & the
reports.</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=920580919-03092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN
class=920580919-03092003>Virginia</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><SPAN
class=920580919-03092003>***************</SPAN></FONT></DIV>
<DIV><FONT size=2><FONT face=Arial><SPAN class=920580919-03092003>Used
in the query to de</SPAN><SPAN
class=920580919-03092003>termine </SPAN><SPAN
class=920580919-03092003>the number of days past
due:</SPAN></FONT></FONT></DIV>
<DIV><FONT size=2><FONT face=Arial><SPAN
class=920580919-03092003></SPAN>PastDue:
DateDiff("d",[DateEntered],Now())</FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><SPAN
class=920580919-03092003>OnCurrent of the form:</SPAN></FONT></DIV>
<DIV>If DateEntered <= DateAdd("d", -5, Date) And StatusID = 1 Then
'Status is pending and review is 5 days past
due.<BR> Me!DateEntered.ForeColor =
lngRed 'Date entered is red<BR>
Me!LateDate.Visible = True ' Show the sad
face<BR> Me.DaysPastDue.Visible =
True 'Show past due label<SPAN class=924281920-03092003><FONT face=Arial
color=#0000ff size=2> </FONT></SPAN></DIV>
<DIV><SPAN class=924281920-03092003><FONT face=Arial color=#008000
size=2> '-- Show number of days over
5 that the project review is late</FONT></SPAN></DIV>
<DIV><SPAN class=924281920-03092003><FONT face=Arial color=#0000ff
size=2> Me.DaysPastDue.Caption =
(Date() - DateEntered) + 5</FONT></SPAN><SPAN
class=924281920-03092003> </SPAN><BR>
Else<BR>
Me!DateEntered.ForeColor = lngBlack 'Review is not past due, Date
Entered is black<BR>
Me!LateDate.Visible = False 'No sad
face<BR>
Me.DaysPastDue.Visible = False 'No past due label.<BR>
End If</DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BODY></HTML>