[AccessD] Third Wednesday of every month

I wrote this a LONG time ago.  When I was just learning VBA.  It's actually
part of the 'current' MiniCalendar example database:

It dumps to 'tblWeekOfDates'.  You give it a starting date, and ending date,
the name of the day, the # of the week (ie, 3rd Wednesday would be 3), and
the indexnumber is something I used to differentiate the results. (So if you
want to run this for different days/weeks, you could 'cluster' the results
under an indexnumber.

Drew

Function fWeekOfDates(StartDate As Date, EndDate As Date, DayName As String,
WeekOf As Long, IndexNumber As Long)
'set up a variable to change from the startdate to the enddate
Dim TestingDate As Date
TestingDate = StartDate
'set up necessary variables to help with determining if a date matches the
criteria
Dim DayNumber As Long
Dim FirstDayCriteria
Dim LastDayCriteria
Dim DayNameCriteria
'This variable will be used as the variable SQL code to insert the date into
the tblWeekOfDates
Dim DateSQL
'This variable will be set to true if the Date Falls within the required
dates
Dim DayNumberCriteria As Boolean
DayNumberCriteria = False
'Set up a boolean variable to stop the loop once the EndDate is checked
Dim StopLoop As Boolean
StopLoop = False
'Start the Date Checking loop
Do Until StopLoop = True
    'Get Date information and set criteria
    DayNumber = Format(TestingDate, "d")
    FirstDayCriteria = 7 * (WeekOf - 1) + 1
    LastDayCriteria = 7 * WeekOf
    DayNameCriteria = Format(TestingDate, "dddd")
    'Set boolean variable to true if the day falls within the specified week
    If DayNumber >= FirstDayCriteria And DayNumber <= LastDayCriteria Then
        DayNumberCriteria = True
    End If
    'if the date is a match for that month (allow the Insert Into SQL to
run)
    If DayName = DayNameCriteria And DayNumberCriteria = True Then
        DateSQL = "INSERT INTO tblWeekOfDates (IndexNumber, DatesSelected)
Values ('" & IndexNumber & "', '" & TestingDate & "');"
        DoCmd.SetWarnings (False)
        DoCmd.RunSQL DateSQL
        DoCmd.SetWarnings (True)
    End If
    DayNumberCriteria = False
    
    If TestingDate = EndDate Then
        StopLoop = True
        Else
        TestingDate = TestingDate + 1
    End If
Loop
End Function



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller
Sent: Monday, July 19, 2004 10:12 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Third Wednesday of every month


Has anyone code some code in hand that would allow me to calculate the
third Wednesday of every month? Or for that matter just the first
Wednesday of a given month (to which I'll just add 14)? I don't think
it's that tough, but thought I'd check to see if someone has it in hand
before I write it.

TIA,
Arthur

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com