Results 1 to 4 of 4

Thread: Help, Date

  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: Help, Date

    Hi all,
    (Sorry my english :$)
    my problem is that i have a table of class with a [Start_Date] and a [Finish_Date] (dd-mm-yyyy) and i choose the days (Monday to Friday) of the week that the class it happens.
    what i whant is the number of day's selected...
    Example:
    Class: 1 ; Start_Date: 23-01-2006; Finish_Date: 20-02-2006; Day1: Monday ; Day2: Friday;

    What i whant but i cant work it out:
    Nº of Monday's between dates: 5
    Nº of Friday's between dates: 4

    Ty all

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    this
    http://www.mvps.org/access/datetime/date0006.htm

    will give you the Number of Work days between 2 dates
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2003
    Posts
    16
    TY for the help

  4. #4
    Join Date
    Nov 2003
    Posts
    16
    hi again,
    well, what i did whant whas the date of the workdays bteween the [Init_Date] and [End_Date] so i can creat records to work with a calendar of attendance. So this was what i come up: (Hope it help's someone. If is something wrong plz reply)

    Private Sub Calc_Days()
    Dim DayC As Date
    Dim DayI As Boolean

    DayI = True
    DayC = Me.Init_Date

    Do While DayC <= Me.End_Date
    Select Case ComboBox_WorkDay ‘Comb that contains “Monday; Tuesday; _Wednesday; Thursday; Friday; Saturday

    Case " Monday "
    If (Weekday(DayC) = 2) And (DayI = True) Then
    DayC = Me.Init_Date
    ElseIf (Weekday(DayC) <> 2) And (DayI = True) Then
    DayI = False
    End If
    If (Weekday(DayC) > 2) And (DayI = False) Then
    DayC = DateAdd("d", 9 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) < 2) And (DayI = False) Then
    DayC = DateAdd("d", 2 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) = 2) And (DayI = False) Then
    DayC = DateAdd("d", 7, DayC)
    If (DayC > Me.End_Date) Then Exit Do
    End If
    DayI = False

    Case " Tuesday "
    If (Weekday(DayC) = 3) And (DayI = True) Then
    DayC = Me.Init_Date
    ElseIf (Weekday(DayC) <> 3) And (DayI = True) Then
    DayI = False
    End If
    If (Weekday(DayC) > 3) And (DayI = False) Then
    DayC = DateAdd("d", 10 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) < 3) And (DayI = False) Then
    DayC = DateAdd("d", 3 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) = 3) And (DayI = False) Then
    DayC = DateAdd("d", 7, DayC)
    If (DayC > Me.End_Date) Then Exit Do
    End If
    DayI = False

    Case " Wednesday "
    If (Weekday(DayC) = 4) And (DayI = True) Then
    DayC = Me.Init_Date
    ElseIf (Weekday(DayC) <> 4) And (DayI = True) Then
    DayI = False
    End If
    If (Weekday(DayC) > 4) And (DayI = False) Then
    DayC = DateAdd("d", 11 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) < 4) And (DayI = False) Then
    DayC = DateAdd("d", 4 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) = 4) And (DayI = False) Then
    DayC = DateAdd("d", 7, DayC)
    If (DayC > Me.End_Date) Then Exit Do
    End If
    DayI = False

    Case " Thursday "
    If (Weekday(DayC) = 5) And (DayI = True) Then
    DayC = Me.Init_Date
    ElseIf (Weekday(DayC) <> 5) And (DayI = True) Then
    DayI = False
    End If
    If (Weekday(DayC) > 5) And (DayI = False) Then
    DayC = DateAdd("d", 12 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) < 5) And (DayI = False) Then
    DayC = DateAdd("d", 5 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) = 5) And (DayI = False) Then
    DayC = DateAdd("d", 7, DayC)
    If (DayC > Me.End_Date) Then Exit Do
    End If
    DayI = False

    Case "Friday"
    If (Weekday(DayC) = 6) And (DayI = True) Then
    DayC = Me.Init_Date
    ElseIf (Weekday(DayC) <> 6) And (DayI = True) Then
    DayI = False
    End If
    If (Weekday(DayC) > 6) And (DayI = False) Then
    DayC = DateAdd("d", 13 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) < 6) And (DayI = False) Then
    DayC = DateAdd("d", 6 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) = 6) And (DayI = False) Then
    DayC = DateAdd("d", 7, DayC)
    If (DayC > Me.End_Date) Then Exit Do
    End If
    DayI = False

    Case " Saturday "
    If (Weekday(DayC) = 7) And (DayI = True) Then
    DayC = Me.Init_Date
    ElseIf (Weekday(DayC) <> 7) And (DayI = True) Then
    DayI = False
    End If
    If (Weekday(DayC) > 7) And (DayI = False) Then
    DayC = DateAdd("d", 14 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) < 7) And (DayI = False) Then
    DayC = DateAdd("d", 7 - Weekday(DayC), DayC)
    If (DayC > Me.End_Date) Then Exit Do
    ElseIf (Weekday(DayC) = 7) And (DayI = False) Then
    DayC = DateAdd("d", 7, DayC)
    If (DayC > Me.End_Date) Then Exit Do
    End If
    DayI = False

    Case Else
    Exit Do
    End Select
    MsgBox "Day: " & DayC
    Loop

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •