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

 

[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

 

[Ken Sheridan]

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!