“Basically, it is meant to be run every third Tuesday and pull all records from the previous period which would be the third Tuesday of the prior month up to the current month's third Monday.”
I personally don’t see nested Iif() statements as being the best solution—the coding becomes extremely error prone. It would seem that, given a date (which can be any date in the current month), you just need a way of determining:
a. The third Tuesday of the prior month.
b. The third Monday of the current month.
The following function (copy/paste to a new module) will do that for you:
To test how this would work within a query:
(1) Create a link to Northwind’s Orders table.
(2) Create a new query and copy/paste this SQL:
(3) Run the query and, when prompted to [enter a date], enter a date between 1-Sep-94 and 30-Apr-96 (the effective range of the Orders table, for the purposes of this illustration). You should be returned all records from the third Tuesday of the month prior to [enter a date] up to the month of the [enter a date] third Monday.
To avoid confusion between date formats, suggest you initially use the Medium Date format shown above.
HTH-
Bob
I personally don’t see nested Iif() statements as being the best solution—the coding becomes extremely error prone. It would seem that, given a date (which can be any date in the current month), you just need a way of determining:
a. The third Tuesday of the prior month.
b. The third Monday of the current month.
The following function (copy/paste to a new module) will do that for you:
Code:
Public Function NthXDay(pDate As Variant, pWDay As Integer, pIncrement As Integer) As Date
'*******************************************
'Name: NthXDay (Function)
'Purpose: Find the nth occurence of a weekday
' within any given month, with Sunday = 1
' through Saturday = 7
'Inputs: (1) To find the 3rd Thursday of Sep 2003
' ? NthXDay(#9/24/03#, 5, 3)
' (2) Specify pIncrement as 6 to return the 'last'
' occurence within a month
' To find the last Thursday of Sep 2003
' ? NthXDay(#9/24/03#, 5, 6)
'Output: (1) 9/18/03
' (2) 9/25/03
'*******************************************
Dim dteDate As Date, newDate As Date, Msg As String
'find the first day of the month or if pIncrement is 6
'(to indicate the last pWDay of the month) the first day
'of the following month
dteDate = DateSerial(Year(DateValue(pDate)), _
Month(DateValue(pDate)) + IIf(pIncrement = 6, 1, 0), 1)
'move to the first pWDay
newDate = dteDate - WeekDay(dteDate) + pWDay + IIf(WeekDay(dteDate) > pWDay, 7, 0)
'move to the specified 7* (pIncrement -1) occurence of pWDay or
'if pIncrement is 6, the preceding occurence (-7) of pWDay
newDate = DateAdd("d", IIf(pIncrement = 6, -7, 7 * (pIncrement - 1)), newDate)
'if pIncrement is 5 and there are not 5 occurences of PWDay in
'the specified month, return an error message, else return the
'calculated date
If Month(newDate) > Month(dteDate) Then
Msg = "There are only four " & Format(WeekDay(pWDay), "dddd") & "'s in " & Format(Month(dteDate), "mmmm")
MsgBox Msg, vbInformation, "Error"
Exit Function
Else
NthXDay = newDate
End If
End Function(1) Create a link to Northwind’s Orders table.
(2) Create a new query and copy/paste this SQL:
Code:
PARAMETERS [enter a date] DateTime;
SELECT Orders.OrderID, Orders.OrderDate, Format(Weekday([orderdate]),"dddd") AS Expr1, [enter a date] AS MyInputDate
FROM Orders
WHERE (((Orders.OrderDate) Between nthxday(DateAdd("m",-1,[enter a date]),3,3) And nthxday([enter a date],2,3)));To avoid confusion between date formats, suggest you initially use the Medium Date format shown above.
HTH-
Bob
