Checking To Make Sure Date Falls On Weekday

[salisha]

I have a problem with checking for weekdays. I am working in MS Access 2000.
I have a combo field called cboCAlendar that has numbers to pick from. On the After Update Event for this field I want to place some code that will add this number to another field called "Assigned Date" and place the new date in the "DueDate" field.
I need some code to check if this date falls on a Sunday or Saturday, and if it does, add the appropriate numbers to it to take make it a weekday date.
I hope this makes sense.
Thanks

 

[Zathras]

You don't need VBA.

If D2 has the selected date and E2 has the number of days in the future, then this formula will return a date Monday thru Friday. Any Saturday or Sunday will be moved later to the following Monday. (A slight variation can move it earlier to the preceding Friday, if that is preferred.):

  =$D$2+$E$2+(WEEKDAY($D$2+$E$2)=1) + (WEEKDAY($D$2+$E$2)=7)*2

Hope this helps.

 

[salisha]

why don't I need vba? I am writing this code in vba. I am using it behind a Ms Access Form.

 

[Zathras]

Sorry, I was hung up in Excel.

Try this:

Option Explicit

Sub test42()
  MsgBox SetDate("02/28/2003", 7)
End Sub

Function SetDate(RefDate As Date, Days As Integer) As Date
  SetDate = RefDate + Days
  If WeekDay(SetDate) = 7 Then
    SetDate = SetDate + 2
  Else
    If WeekDay(SetDate) = 1 Then
      SetDate = SetDate + 1
    End If
  End If
End Function

Hope it helps.

 

[salisha]

thank u...i'm going to try it now...