Help Please

[SJJ via AccessMonster.com]

I have a set of dates ( for 1 yr) , from those dates how can i pick up the
2nd friday in every month. what is the criteria for the query. so it picks
all the 2nd mondays

any ideas please

thanks

 

[Tom Ellison]

Dear SJJ:

I would think a simple approach would be to pick the first Monday and add 7
days. Use DatePart to filter it to shows only Mondays, MIN() to find the
first Monday, and DateAdd to add the 7 days.

Finding the 5th Monday would be a bit trickier, as there isn't always one.

Tom Ellison

 

[Guest]

I produced the following function a while ago to do this. First paste the
function into a standard module in the database:

Public Function DayInMonth(intWeekday As Integer, intNthDay As Integer,
Optional varDate) As Boolean

' Returns True if vardate is intNthDay instance of intWeekDay in month.
' Call by passing day of week as integer number (Sunday = 1),
' integer number for instance of that day in month and, optionally,
' date in question into function.
' If third argument omitted defaults to current date.

Dim dtmStart As Date, dtmDate As Date
Dim intCount As Integer

' set today's date as defult if optional third argument missing
If IsMissing(varDate) Then varDate = VBA.Date

' get first day of month
dtmStart = DateSerial(Year(varDate), Month(varDate), 1)

' loop through each day in month up to required date
' and count number of instances of relevant day of week
For dtmDate = dtmStart To varDate
If Weekday(dtmDate) = intWeekday Then
intCount = intCount + 1
End If

' set return value to True if loop is
' at required date and it is required
' day of week and it is required instance
' of that day in month
DayInMonth = (dtmDate = varDate _
And Weekday(dtmDate) = intWeekday _
And intCount = intNthDay)
Next dtmDate

End Function

Then call it in a query like so to return rows for the second Friday per
month:

SELECT *
FROM YourTable
WHERE DayInMonth(6,2,YourDateField);

For the second Mondays use:

SELECT *
FROM YourTable
WHERE DayInMonth(2,2,YourDateField);

While you can use this function directly in a query like this its not very
efficient and, if you need these dates regularly a better solution is to
create a Calendar table over a period of years. This is simply a list of all
dates and can be easily created by serially filling a column in Excel and
importing it into Access, or with some VBA code to insert rows into the
table. Once you have this basic calendar table you can add Boolean (Yes/No)
columns to it SecondFriday, SecondMonday say and then use the above function
in an update query to set these to TRUE as appropriate. Armed with this
calendar table you can then join it to any table containing dates to return
only the rows in that table on the second Fridays and/or Mondays, e.g.

SELECT Orders.*
FROM Orders INNER JOIN Calendar
ON Orders.OrderDate = Calendar.CalDate
WHERE SecondMonday OR SecondFriday;

You can of course add other useful columns to the calendar table to record
other significant dates which you might want to test dates in other tables
against.

Ken Sheridan
Stafford, England

 

[SJJ via AccessMonster.com]

i have a calender table with all the dates for next few years. but i dont no
how to pick up the 2nd monday from the list. thats what i want to no.
possibly using a query and criteria.

any simple solution.

 

[Gary Walter]

i have a calender table with all the dates for next few years. but i dont
no
how to pick up the 2nd monday from the list. thats what i want to no.
possibly using a query and criteria.

Hi SJJ,

If you have a "tblCalendar" with
all the dates listed in a "datefield"
and you want to return only the
2nd Monday's, one way might be:

SELECT [datefield]
FROM tblCalendar
WHERE
WeekDay([datefield]) =2
AND
Day([datefield]) BETWEEN 8 AND 14

the WeekDay=2 would select only Mondays
(assuming Sunday is first day of "your week")

and the second Monday would only occur
between the 8th and 14th of the month.

good luck,

gary

 

[Guest]

You can either use my function in the query's WHERE clause or more
efficiently you can call the Weekday and Day functions as Gary suggests, but
I'd suggest you add a Boolean SecondMonday column to the calendar table and
set the appropriate rows to TRUE with an UPDATE query either using my
function (which is designed to be a generic solution to do this) or a WHERE
clause like Gary's. You then simply have to use a WHERE clause of:

WHERE SecondMonday

this avoiding the inefficiency of the calls to my function or the built in
Weekday and day functions, and hence improving performance. Calendar tables
with columns for custom purposes like this are used quite a lot in SQL,
particularly in processing financial transactions where dates are often
interrelated, e.g. something may become due '15 working days after a date'.
This can be done with procedural code, but by precomputing this for all dates
in the calendar table and storing the values in a separate column it can be
done far more efficiently without the overhead of the procedural computations
each time.

Joe Celko has a chapter on such 'auxiliary' tables and their uses in his
book 'SQL for Smarties'. He points out that they are not really part of the
model, but adjuncts to it. Often they break many of the rules of normal
table design! Doing as I suggest above, for instance, would be the cardinal
sin of 'encoding data as column headings' in a table which was part of the
database's logical model.

Ken Sheridan
Stafford, England

 

[Ken Snell \(MVP\)]

Here is a function that I've written to get the specific date of a month,
such as you seek:

Function GetActualDateForGenericDayOfMonth(lngYear As Long, _
lngMonthNumber As Long, lngWeekDayNumber As Long, _
lngWeekDayOccurrenceOfMonth As Long) As Date
' Ken Snell - July 1, 2004
'***THIS FUNCTION RETURNS THE ACTUAL DATE FOR A GENERIC
'***DAY OF A MONTH (e.g., THE THIRD MONDAY OF A MONTH).
'***THE VARIABLES PROVIDED TO THE FUNCTION:
'*** lngYear - the year for the date.
'*** lngMonthNumber - the number of the month for the date
'*** (e.g., January = 1, February = 2, etc.).
'*** lngWeekDayNumber - the number of the weekday for the date
'*** (e.g., Sunday = 1, Monday = 2, etc.).
'*** lngWeekDayOccurrenceOfMonth - the order number of the weekday for
the date
'*** (e.g., 1 = first occurrence of that weekday in the month,
'*** 2 = second occurrence of that weekday in the month, etc.).
'***IF AN ERROR OCCURS DURING THE EVALUATION OF THE DATE,
'***A VALUE OF ZERO IS RETURNED BY THE FUNCTION.

On Error Resume Next
GetActualDateForGenericDayOfMonth = DateSerial(lngYear, lngMonthNumber, _
8 - DatePart("w", DateSerial(lngYear, lngMonthNumber, 1), _
1 + lngWeekDayNumber Mod 7) + (lngWeekDayOccurrenceOfMonth - 1) * 7)
If Err.Number <> 0 Then GetActualDateForGenericDayOfMonth = 0
Err.Clear
End Function

 

[Ant]

Hi Ken,

I was just posting my question (Above when in date order) and noticed your
name "Snell" My name is Tony Snell (Ant) Not many of us about so wounded if
you originate from Sheffield England?

Just a thought

Ant

Here is a function that I've written to get the specific date of a month,
such as you seek:

Function GetActualDateForGenericDayOfMonth(lngYear As Long, _
lngMonthNumber As Long, lngWeekDayNumber As Long, _
lngWeekDayOccurrenceOfMonth As Long) As Date
' Ken Snell - July 1, 2004
'***THIS FUNCTION RETURNS THE ACTUAL DATE FOR A GENERIC
'***DAY OF A MONTH (e.g., THE THIRD MONDAY OF A MONTH).
'***THE VARIABLES PROVIDED TO THE FUNCTION:
'*** lngYear - the year for the date.
'*** lngMonthNumber - the number of the month for the date
'*** (e.g., January = 1, February = 2, etc.).
'*** lngWeekDayNumber - the number of the weekday for the date
'*** (e.g., Sunday = 1, Monday = 2, etc.).
'*** lngWeekDayOccurrenceOfMonth - the order number of the weekday for
the date
'*** (e.g., 1 = first occurrence of that weekday in the month,
'*** 2 = second occurrence of that weekday in the month, etc.).
'***IF AN ERROR OCCURS DURING THE EVALUATION OF THE DATE,
'***A VALUE OF ZERO IS RETURNED BY THE FUNCTION.

On Error Resume Next
GetActualDateForGenericDayOfMonth = DateSerial(lngYear, lngMonthNumber, _
8 - DatePart("w", DateSerial(lngYear, lngMonthNumber, 1), _
1 + lngWeekDayNumber Mod 7) + (lngWeekDayOccurrenceOfMonth - 1) * 7)
If Err.Number <> 0 Then GetActualDateForGenericDayOfMonth = 0
Err.Clear
End Function

 

[Ken Snell \(MVP\)]

My primary ancestors came from Germany, I'm told. Don't know of any
relatives from England...sorry!