DateAdd for The current month and day of week

[GDC1970]

I need to know the MS Access VBA code to produce a DateAdd to give me one month from today and the same day of the week.

[twinnyfo]

GDC1970,

Please provide a little more information. Do you mean, for example, today is October 17, but it is the third Friday of the Month, so, one month from now would be November 21, as it is the third Friday of the month?

How do you handle special situations, such as a fifth weekday of one month with no corresponding next month?

Also, what is the nature of this question, which also might help us provide either different solutions or different options that also might suit your needs....

[GDC1970]

Thats exactly what im looking for. There would be no special exceptions. I am scheduling activities for a treatment program and they are monthly on the 3rd fri of month, every month.

[GDC1970]

Or if my start date is a monday the 2nd monday of the month, I want it to add one month later on monday the 2nd monday of the month.
So I want it to use the start date info to produce the next month and day of week

[twinnyfo]

So, now I have to ask, what have you tried so far?

How are you creating these dates? Is it on a form? Are you creating a schedule in Access? How many dates do you schedule in advance?

Also, what kind of experience do you have with MS Access and VBA?

If you follow where my questions are leading, there may be more involved than just the simple answer of getting a list of every 2nd Monday or 3rd Friday of the month. I am trying to make sure you have something you can use and understand for your next project. But, we also need to know what you have envisioned for how this will work. I don't want to start leading you down one path and then find out that this is not what you need. I hope you understand.

[GDC1970]

I am pretty good with VBA but not and expert. I do use a form to select the activity I want, then enter a start and end date. I usually schedule for 2 years at a time. This code works great for weekly, bi weekly scheduling, but I need to incorporate the monthly.

Here is my current code:

Public Sub Assign_Groups_Click()
 
   Dim NumLoops As Integer
   Dim Freq As Variant
   Dim Interval As Variant
   Dim DateCounter As Date
   Dim Counter As Integer
   Dim TopicID As Integer
   Dim HolidayCount As Variant
 
 'Frequency/Interval
    Freq = Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![Frequency]
    Select Case Freq
        Case "Yearly"
           Interval = 365
        Case "Monthly"
           Interval = 1
        Case "Bi Weekly"
           Interval = 14
        Case "Weekly"
           Interval = 7
        Case "Daily"
           Interval = 1
    End Select
 
    NumLoops = Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![Total Topics]
 
     DateCounter = Me.StartDate
     Me.txtDateCounter = DateCounter
 
     Do While DateCounter <= Last_Date
     TopicID = 1
     Counter = 1
     Me.TopicIDCounter = TopicID
     DoCmd.SetWarnings False
     DoCmd.OpenQuery "QryAddActivityDate"
     For Counter = 1 To NumLoops - 1
     DateCounter = dateadd("D", Interval, DateCounter)
     Me.txtDateCounter = DateCounter
 
     TopicID = TopicID + 1
     Me.TopicIDCounter = TopicID
     DoCmd.OpenQuery "QryAddActivityDate"
     Next Counter
     DateCounter = dateadd("D", Interval, DateCounter)
     Me.txtDateCounter = DateCounter
     Loop
 
     DoCmd.SetWarnings True
     Me.FrmScheduleActivitiesSUB.Requery
     Me.FrmScheduleActivitiesSUB.Visible = True
 
     End Sub

[twinnyfo]

Great explanation! I'm also glad you have a basic understanding of VBA.

Let me take a look at what you have and see what I can come up with to guide you along!

P.S.: Don't forget to use the Code Tags for your code!

[GDC1970]

I really appreciate your help

[twinnyfo]

Try modifying this Code to suit your needs:

    Dim DayOfWeek As Integer
    Dim DayOfMonth As Integer
 
    NumLoops = Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![Total Topics]
 
    DateCounter = Me.StartDate
    Me.txtDateCounter = DateCounter
 
    DayOfWeek = Weekday(DateCounter)
    '2nd Monday = Day Between 8 and 14 and WeekDay = 2
    '3rd Friday = Day Between 15 and 21 and WeekDay = 6
    Select Case DayOfWeek
        Case 2
            DayOfMonth = 8
        Case 3
            DayOfMonth = 15
    End Select
 
    If DayOfWeek = 2 Or DayOfWeek = 6 Then
        For Counter = 1 To NumLoops
            Me.TopicIDCounter = Counter
            DateCounter = CDate( _
                DayOfMonth & "-" & _
                Format(DateAdd("m", Interval, DateCounter), "mmm") & "-" & _
                Year(DateAdd("m", Interval, DateCounter)) _
                )
            Do While Not Weekday(DateCounter) = DayOfWeek
                DateCounter = DateCounter + 1
            Loop
            Me.txtDateCounter = DateCounter
            DoCmd.OpenQuery "QryAddActivityDate"
        Next Counter
    Else
        MsgBox "Your Start Date must be a Monday or a Friday!"
    End If

Please note that I stripped your code of the other possibilities, so you will have to insert this somewhere in between.

Notes:

Lines 1-2: Add these declarations to your variables.

Line 9: This assumes that you always begin the sessions on a Monday or Friday. If not, then you should change your start date.

Lines 10-17: Some notes behind the logic. Then, test for which day of the week we are starting on. Based on the 7 day week, the second Monday can start no earlier than the 8th of the month; the third Friday, no earlier than the 15th. These are our starting points.

Lines 19, 33-35: If this is not a Monday or Friday, inform the user.

Lines 20, 32: Your code had some strange calculating of the counter and dates. Id had quite a bit of redundancy. This is a trimmed version which keeps the looping to just the standard number of iterations.

Line 21: According to your Code, TopicID was always the same as the Counter. No need for separate variables....

Lines 22-26: Look very carefully at this code, because it is based on some of your original calculations for the DateAdd Function. We use the starting date from Lines 10-17, then the Month indicated by adding a month to our date and the Year of that same date. Assign this value to our DateCounter, which is not used for anything else until it meets other criteria.

Lines 27-29: If the WeekDay identified by DateCounter does not match the desired Weekday, then add one to the DateCounter until it does.

Lines 30-31: When DateCounter meets our criteria, use it for the text boxes on our form (and I assume for your Query).

I hope this hepps! A nice little exercise in logic and math!

[GDC1970]

Thanks for your help, im trying the code and im getting an error on:

DateCounter = CDate(DayOfMonth & "-" & Format(dateadd("m", Interval, DateCounter), "mmm") & "-" & Year(dateadd("m", Interval, DateCounter))

[GDC1970]

I added a ) to:
DateCounter = CDate(DayOfMonth)
I think it was missing, the code is not red. But it does stop and debug.

[GDC1970]

I changed
Dim DateCounter As Variant instead of As Date and it does not debug on this line.
Now it debugs on:
Do While Not Weekday(DateCounter) = DayOfWeek

[twinnyfo]

Please post your entire sub again. Anything less and we don't know what you have changed or what you have kept and how you have modified the code.

Thanks!

[GDC1970]

I reworked the code and I got it working great. I really appreciate all your help. This is going to make a huge difference.

[twinnyfo]

Great! I'm glad that we could be of service!