Hi all,

I need some help with Access coding. I am designing an application which 
works out the installation dates for a required job depending on the 
number of days the installation will take. 

There is a dtmInstallFrom field which should pick up 'date()', however if 
the date() is a Saturday or Sunday then Mondays date should appear.

There is also a dtmInstallTo field which should take the value from the 
dtmInstallFrom field, add the number of days required for the job 
(intTotalDays) and dive the date of when the installation should be 
complete. Again if the finish date falls on the weekend then the next 
Monday date should be entered.

Please could you give me some sort of guideline to work upon.

Thanks
James

function weekdate (dtmDate) as date

dim weekday as date


if format (dtmDate,"dddd") = "Saturday" then
   	 weekday =  dateadd("d",2,dtmDate)
elseif format(dtmDate,"dddd") = "Sunday"
 	 weekday =  dateadd("d",1,dtmDate)
else	weekday = dtmdate
end if

weekdate = weekday
	
Steve K


-----Original Message-----
From: James Potter [mailto:questionaccess@h...]
Sent: 25 November 2002 14:12
To: Access
Subject: [access] Coding with Days of the Week


Hi all,

I need some help with Access coding. I am designing an application which 
works out the installation dates for a required job depending on the 
number of days the installation will take. 

There is a dtmInstallFrom field which should pick up 'date()', however if 
the date() is a Saturday or Sunday then Mondays date should appear.

There is also a dtmInstallTo field which should take the value from the 
dtmInstallFrom field, add the number of days required for the job 
(intTotalDays) and dive the date of when the installation should be 
complete. Again if the finish date falls on the weekend then the next 
Monday date should be entered.

Please could you give me some sort of guideline to work upon.

Thanks
James

Original message from James Potter:
I need some help with Access coding. I am designing an application which
works out the installation dates for a required job depending on the
number of days the installation will take.

Response:

The best approach is to use the function DateAdd('d', YourDate, 5) - this
will add the five days to your date but will not ignore any non-working
days.

If you are only concerned with ignoring Saturday and Sunday, the code below
will do.



Public Function CalcWeekdays( pDateIn As Date, pDays As Integer) As Date
  ' Comments  : Calculate the number of weekdays to add or subtract to a
date
  '             Weekend dates are not counted in adding/subtracting days
  ' Parameters: pDateIn - Starting date
  '             pDays - Number of days to add (negative to subtract)
  ' Returns   : Original date plus the number of weekdays added
Dim intCounter As Integer, intDirection As Integer, datNewDate As Date
Dim lngWeeks As Long, intDaysLeft As Integer

On Error GoTo PROC_ERR

datNewDate = pDateIn
' work out whether pDays is negative or positive and set intDirection
accordingly
If pDays > 0 Then
  intDirection = 1
Else
  intDirection = -1
End If

lngWeeks = Fix(Abs(pDays) / 5)

If lngWeeks > 0 Then
  datNewDate = datNewDate + lngWeeks * 7 * intDirection
End If

intDaysLeft = Abs(pDays) - lngWeeks * 5
' step through the dates in the period from pDateIn plus or minus pDays
' if the Weekday of a date is a Saturday or Sunday
' (1 or 7 when week starts on Sunday)
' it is ignored
For intCounter = 1 To intDaysLeft
  datNewDate = datNewDate + 1 * intDirection

  If intDirection > 0 Then
    ' Increment date
    If WeekDay(datNewDate) = 7 Then
      datNewDate = datNewDate + 2
    End If
  Else
    ' Decrement date
    If WeekDay(datNewDate) = 1 Then
      datNewDate = datNewDate - 2
    End If
  End If
Next

CalcWeekdays = datNewDate

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "CalcWeekdays"
  Resume PROC_EXIT

End Function

If you need to ignore any non-working days (e.g. Public Holidays), you need
more code and a look-up table for holidays - the technique there is to do
as above (i.e. ignore Saturday and Sunday) but check against the holiday
table to confirm whether any weekday (Monday to Friday date) is a holiday -
if so ignore this.








*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s).
 If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return: you should
not disclose the 
contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an authorised individual, nothing
contained in this e-mail is
 intended to create binding legal obligations between us and opinions expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Personal Investment Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 (also regulated by IMRO) - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

James,

<< There is a dtmInstallFrom field which should pick up 'date()', however 
if the date() is a Saturday or Sunday then Mondays date should appear.

There is also a dtmInstallTo field which should take the value from the 
dtmInstallFrom field, add the number of days required for the job 
(intTotalDays) and dive the date of when the installation should be 
complete. Again if the finish date falls on the weekend then the next 
Monday date should be entered. >>

Here's another simple approach.  Suppose the textbox where dtmInstallTo 
shown is Me.txtInstallTo and dtmInstallFrom is shown in 
Me.txtInstallFrom.  You would just loop through the value of Weekday() 
until you avoid a weekend.  Value 1 = Sunday; Value = 7 = Saturday.

  Me.txtInstallFrom = Date()
  Do While Weekday(Me.txtInstallFrom) < 2 Or Weekday(Me.txtInstallFrom) > 6
     Me.txtInstallFrom = DateAdd("d", 1, Me.txtInstallFrom)
  Loop

  Me.txtInstallTo = DateAdd("d", intTotalDays, Me.txtInstallFrom)
  Do While Weekday(Me.txtInstallTo) < 2 Or Weekday(Me.txtInstallTo) > 6
     Me.txtInstallTo = DateAdd("d", 1, Me.txtInstallTo)
  Loop


Greg

Hi All,

I have one datefrom date field which needs to be added to an integer value 
(totaldays) to give the dateto date field. How do I write the code so that 
both Saturday and Sunday are excluded from the calculation.

Thanks
James

Hi James,

Function DateAddW(ByVal TheDate, ByVal Interval)

   Dim Weeks As Long, OddDays As Long, temp As String

   If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
              VarType(Interval) > 5 Then
      DateAddW = TheDate
   ElseIf Interval = 0 Then
      DateAddW = TheDate
   ElseIf Interval > 0 Then
      Interval = Int(Interval)

   ' Make sure TheDate is a workday (round down).

      temp = Format(TheDate, "ddd")
      If temp = "Sun" Then
         TheDate = TheDate - 2
      ElseIf temp = "Sat" Then
         TheDate = TheDate - 1
      End If

   ' Calculate Weeks and OddDays.

      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate + (Weeks * 7)

  ' Take OddDays weekend into account.

      If (DatePart("w", TheDate) + OddDays) > 6 Then
         TheDate = TheDate + OddDays + 2
      Else
         TheDate = TheDate + OddDays
      End If

      DateAddW = TheDate
   Else                               ' Interval is < 0
      Interval = Int(-Interval) ' Make positive & subtract later.

   ' Make sure TheDate is a workday (round up).

      temp = Format(TheDate, "ddd")
      If temp = "Sun" Then
         TheDate = TheDate + 1
      ElseIf temp = "Sat" Then
         TheDate = TheDate + 2
      End If

   ' Calculate Weeks and OddDays.

      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate - (Weeks * 7)

   ' Take OddDays weekend into account.

      If (DatePart("w", TheDate) - OddDays) > 2 Then
         TheDate = TheDate - OddDays - 2
      Else
         TheDate = TheDate - OddDays
      End If

      DateAddW = TheDate
    End If

End Function


>From: "James Potter" <questionaccess@h...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Coding with Days of the Week
>Date: Fri, 29 Nov 2002 10:50:11
>
>Hi All,
>
>I have one datefrom date field which needs to be added to an integer value
>(totaldays) to give the dateto date field. How do I write the code so that
>both Saturday and Sunday are excluded from the calculation.
>
>Thanks
>James


_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963