Date - Day of Week in Month

[AideenWolf]

Windows XP - Access 2003

I've been researching this for about 3 weeks now and I'm no closer then when I started so any help you all give me will save my hair. I'm VERY new and learning as I go so bare with me.

What I want to do is generate a new record for each date an event is held on. The events are reoccuring events. Daily, weekly, etc.


What I've done so far:

I have a foum that the user enters the Start date and End Date.
They have an option to pick Daily, Weekly, Bi-Weekly, Day in Month, or Week in Month from a Value Listbox.
Then they select (via a checkbox) the weekday they want the event to fall on.

Daily - Generates the dates from start to end date. Each date is it's own record. (works)

Weekly & Bi-Weekly - Generates the dates from start to end date based on which weekday box is checked. Each date is it's own record. (works)
- User can select one or more weekdays (IE Monday or M, W & F) via the boxes and it will generated a record for each of those days between the start and end date.

Date in Month - If the start date is Nov 12, 2006 and the end is Jan 12, 2007. It will generate the records for Nov 12, Dec 12, & Jan 12. (Works)


My problem:

Week in Month - If The conferance is every 2nd Tuesday of each month.
- User selects Tuesday checkbox
- inputs start date & end date. (Nov 7 & Jan 9th)
- The three records should be Nov, Dec 5, Jan 9.

The code itself should be able to pull which week Nov 7 is on and spit out Dec 5 by itself.


Solution: (Not sure how to code it)
MyWeek = The week # from the start date - 1
NewDate = Dateadd("Find the first weekday (tuesday) of next month", MyWeek, StartDate)


Thanks for any help you all can give.

[NeoPa]

Do you have any example code to modify?
This will save time coding what's already done and also give hints as to any gaps in your explanation.
What format do you need the output in for instance?

[AideenWolf]

To check for the weekday:

Function SkipThisDay(intDow As Integer) As Boolean
    SkipThisDay = False
    Select Case intDow
        Case 1 'Sunday
            If Me![Sunday] <> True Then SkipThisDay = True
        Case 2  'Monday
            If Me![Monday] <> True Then SkipThisDay = True
        Etc for each weekday.
    End Select
End Function

Code to create the new record.

    Dim dblCurrentTime As Date
    Dim dblBeginTime As Date
    Dim dblEndTime As Date
 
    While dblCurrentDate <= dblEndDate
        If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
            rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
            If rstSchedule.NoMatch Then
                rstSchedule.AddNew
                rstSchedule![TicketID] = lngTicketID          'pulls from the form
                rstSchedule![Date] = dblCurrentDate
                rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM")          'pulls from the form
                rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM")          'pulls from the form
                rstSchedule![Ports] = lngPorts           'pulls from the form
                rstSchedule.Update
                fPreviousRecs = False
            Else
                lngScheduleID = rstSchedule![ScheduleID]
            End If
        End If
        Select Case Me![Type]
            Case "Week In Month"            'Monthly Week In Month
                dblCurrentDate = DateAdd("m", 1, dblCurrentDate) ' which isn't right but it's temp till I figure this out. At least it gives the right month.
            Case "Date In Month"            'Monthly Date In Month
                dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
            Case "Bi-Weekly"                'Bi-Weekly
                dblCurrentDate = dblCurrentDate + 14
            Case "Weekly"                   'Weekly
                dblCurrentDate = dblCurrentDate + 1
            Case "Daily"                    'Daily
                dblCurrentDate = dblCurrentDate + 1
        End Select
    Wend

Do you need more? As I said I'm relitivly new and learning as I'm going so I'm not compleatly sure what is relivent.

Do you have any example code to modify?
This will save time coding what's already done and also give hints as to any gaps in your explanation.
What format do you need the output in for instance?

[NeoPa]

Try this :

    Dim dblCurrentTime As Date
    Dim dblBeginTime As Date
    Dim dblEndTime As Date
    Dim intWeekday As Integer, intWeekNo As Integer, intDayNo As Integer
 
    If [Type] = "Week In Month" Then
        intWeekday = Weekday(dblCurrentDate)
        intWeekNo = (Day(dblCurrentDate) - 1) Mod 7
    End If
    While dblCurrentDate <= dblEndDate
        If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
            rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
            If rstSchedule.NoMatch Then
                rstSchedule.AddNew
                rstSchedule![TicketID] = lngTicketID          'pulls from the form
                rstSchedule![Date] = dblCurrentDate
                rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM")          'pulls from the form
                rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM")          'pulls from the form
                rstSchedule![Ports] = lngPorts           'pulls from the form
                rstSchedule.Update
                fPreviousRecs = False
            Else
                lngScheduleID = rstSchedule![ScheduleID]
            End If
        End If
        Select Case Me![Type]
            Case "Week In Month"            'Monthly Week In Month
                dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
                intDayNo = Weekday(Format(dblCurrentDate, "1 mmmm yyyy"))
                If intDayNo > intWeekday Then intDayNo = intDayNo - 7
                intDayNo = 1 + (7 * intWeekNo) + (intWeekday - intDayNo)
                dblCurrentDate = CDate(intDayNo & _
                                       Format(dblCurrentDate, " mmmm yyyy"))
            Case "Date In Month"            'Monthly Date In Month
                dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
            Case "Bi-Weekly"                'Bi-Weekly
                dblCurrentDate = dblCurrentDate + 14
            Case "Weekly"                   'Weekly
                dblCurrentDate = dblCurrentDate + 1
            Case "Daily"                    'Daily
                dblCurrentDate = dblCurrentDate + 1
        End Select
    Wend

NB. Date fields are better named dat????? rather than dbl which gives a false impression.
I think this should work for you, but I can't test it for real.
Some nice code in here btw.
I'm sorry my additions are not documented - please ask if you have any specific questions.

[AideenWolf]

To be honest a majorty of the code is snipped from one place or another and put in and fiddled with till it works. To be honest I've never thought to change "dbl" to "dat" in fear I would have to start from square one.

The modification you gave me sorta worked

Inserts the first date then finds the second week in the month and creates the new date. So while step in the right direction (thank you so much btw) not quite there.

What does the the bold part in this?
intWeekNo = (Day(dblCurrentDate) - 1) Mod 7

the "Mod" was the only thing I didn't get and I can't seem to pull it up refrance of it on the web to research.

[NeoPa]

What does the bold part in this mean?
intWeekNo = (Day(dblCurrentDate) - 1) Mod 7

the "Mod" was the only thing I didn't get and I can't seem to pull it up or reference of it on the web to research.

Modulo arithmetic.
Unfortunately it should have read :

intWeekNo = (Day(dblCurrentDate) - 1) \ 7

Mod Operator


Used to divide two numbers and return only the remainder.

Syntax

result = number1 Mod number2

The Mod operator syntax has these parts:

Part Description
result Required; any numeric variable.
number1 Required; any numeric expression.
number2 Required; any numeric expression.



Remarks

The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result. For example, in the following expression, A (result) equals 5.

A = 19 Mod 6.7

Usually, the data type of result is a Byte, Byte variant, Integer, Integer variant, Long, or Variant containing a Long, regardless of whether or not result is a whole number. Any fractional portion is truncated. However, if any expression is Null, result is Null. Any expression that is Empty is treated as 0.

\ Operator Example
This example uses the \ operator to perform integer division.

Dim MyValue
MyValue = 11 \ 4 ' Returns 2.
MyValue = 9 \ 3 ' Returns 3.
MyValue = 100 \ 3 ' Returns 33.

[AideenWolf]

Strange that it works correctly with \ but not with mod.

The only error I have now is if something starts on the 5th week in a month. It should return basicly loop itself back to the last week.

4th Mon: Jan 22, Feb 26, Mar 26, Apr 23
5th Mon: Jan 29, Feb 26, Mar 26, Apr 23

[NeoPa]

Strange that it works correctly with \ but not with mod.

No it's not - that's exactly what you'd expect.
Mod was my mistake - it always should have been \.

The only error I have now is if something starts on the 5th week in a month. It should return basically loop itself back to the last week.

4th Mon: Jan 22, Feb 26, Mar 26, Apr 23
5th Mon: Jan 29, Feb 26, Mar 26, Apr 23

Now you're being greedy :)

    Dim dblCurrentTime As Date
    Dim dblBeginTime As Date, dblEndTime As Date, datTmp As Date
    Dim intWeekday As Integer, intWeekNo As Integer, intDayNo As Integer
 
    If [Type] = "Week In Month" Then
        intWeekday = Weekday(dblCurrentDate)
        intWeekNo = (Day(dblCurrentDate) - 1) \ 7
    End If
    While dblCurrentDate <= dblEndDate
        If (Not SkipThisDay(DatePart("w", dblCurrentDate))) Then
            rstSchedule.FindFirst "[TicketID]=" & Me![TicketID] & " AND [Date]= #" & Format(dblCurrentDate, "mm-dd-yy") & "#"
            If rstSchedule.NoMatch Then
                rstSchedule.AddNew
                rstSchedule![TicketID] = lngTicketID          'pulls from the form
                rstSchedule![Date] = dblCurrentDate
                rstSchedule![StartTime] = Format(dblBeginTime, "hh:mm AMPM")          'pulls from the form
                rstSchedule![EndTime] = Format(dblEndTime, "hh:mm AMPM")          'pulls from the form
                rstSchedule![Ports] = lngPorts           'pulls from the form
                rstSchedule.Update
                fPreviousRecs = False
            Else
                lngScheduleID = rstSchedule![ScheduleID]
            End If
        End If
        Select Case Me![Type]
            Case "Week In Month"            'Monthly Week In Month
                datTmp = dblCurrentDate
                dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
                intDayNo = Weekday(Format(dblCurrentDate, "1 mmmm yyyy"))
                If intDayNo > intWeekday Then intDayNo = intDayNo - 7
                intDayNo = 1 + (7 * intWeekNo) + (intWeekday - intDayNo)
                'Handle invalid dates
                On Error Resume Next
                datTmp = CDate(intDayNo & Format(dblCurrentDate, " mmmm yyyy"))
                On Error GoTo 0
                'If setting datTmp failed then it still has last month's date in it
                If Month(datTmp) = Month(dblCurrentDate) Then
                    dblCurrentDate = datTmp
                Else
                    dblCurrentDate = CDate(intDayNo - 7 & _
                                           Format(dblCurrentDate, " mmmm yyyy"))
                End If
            Case "Date In Month"            'Monthly Date In Month
                dblCurrentDate = DateAdd("m", 1, dblCurrentDate)
            Case "Bi-Weekly"                'Bi-Weekly
                dblCurrentDate = dblCurrentDate + 14
            Case "Weekly"                   'Weekly
                dblCurrentDate = dblCurrentDate + 1
            Case "Daily"                    'Daily
                dblCurrentDate = dblCurrentDate + 1
        End Select
    Wend

[AideenWolf]

No it's not - that's exactly what you'd expect.
Mod was my mistake - it always should have been \.

Now you're being greedy :)

Naw, never greedy just learn by looking at code.. Got about half of what you posted before I was stumped and came back to see if there was anything else. Worked like a charm! Thank you so much for your help NeoPa.

[NeoPa]

No problem AW.
If there's anything you don't understand you only have to ask.
The only danger is that I may waffle on a bit rather than not explain enough.