[AccessD] Third Wednesday of every month

Thanks, Drew and Gustav. I ended up whipping it together while waiting
for a reply. Mine isn't as flexible but is simple to use and does what I
need at the moment. I'll revisit it during refactoring:

<code>
'-----------------------------------------------------------------------
----------------
' Procedure : Bom
' DateTime  : 12/22/2003 07:58
' Author    : Arthur Fuller
' Purpose   : return the first of the month specified by the date passed
in
' Notes     :
'           :
' Revisions :
'           :
'-----------------------------------------------------------------------
----------------
'
Public Function Bom(datThis As Date) As Date
    Dim d As Integer
    d = Day(datThis) - 1
    Bom = datThis - d
End Function

'-----------------------------------------------------------------------
----------------
' Procedure : ThirdWednesday
' DateTime  : 07/19/2004
' Author    : Arthur Fuller
' Purpose   : return the third Wednesday of the month specified by the
date passed in
' Notes     : assumes you will in the first of the month
'           : WeekDay() begins on Sunday unless you pass an argument
' Revisions :
'           :
'-----------------------------------------------------------------------
----------------
'
Public Function ThirdWednesday(datThis As Date) As Date
    Dim retValue As Date
    Dim d As Integer
    Dim temp As Integer
    
    d = Weekday(datThis)
    Select Case d
        Case 1
            temp = 3
        Case 2
            temp = 2
        Case 3
            temp = 1
        Case 4
            temp = 0
        Case 5
            temp = 6
        Case 6
            temp = 5
        Case 7
            temp = 4
    End Select
    'Now we know the first Wednesday of the month
    ThirdWednesday = datThis + temp + 14
End Function
</code>

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
DWUTKA at marlow.com
Sent: Monday, July 19, 2004 11:43 AM
To: accessd at databaseadvisors.com
Subject: RE: [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
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com