NOT Particular Weekday in a Month but the opposite

[Divit]

I want my code to tell me that (today) April 12th is the second
wednesday of the month. And only thing I can find is the function to
tell me what date belongs to the 2nd day in a given month and year.

Please help
regards
ScuBart

[pietlinden@hotmail.com]

Re: NOT Particular Weekday in a Month but the opposite

you'd have to write a function to do it. Do something like...

1. get the day of the first day of the month.
2. find the number of weeks between the two dates
3. then from that you should be able to get the number of Wednesdays or
whatever.

Look up DateAdd, DateDiff, MOD, and that should do it.

[Rich P]

Re: NOT Particular Weekday in a Month but the opposite

Plant this code in a standard code module. Place your mouse cursor
inside of someSub(). Press the F5 key. This will display the given day
which the built-in Date() function returns and if it is the 1st, 2nd,
3rd... occurence of that day in that month. You can use any date value
for the argument.

'------------------------------------------------------
Sub someSub()
Dim d1 As Date, arrSuffix As Variant
arrSuffix = Array("", "1st", "2nd", "3rd", "4th", "5th")
d1 = Date '--d1 can be any date you want: d1=#3/13/06#
Debug.Print arrSuffix(DayOf MonthCount(d1)) & " " & _
WeekdayName(Wee kday(d1))
End Sub


Function DayOfMonthCount (d1 As Date) As Integer
Dim i As Integer, j As Integer, k As Integer, d2 As Date
d2 = d1 - Day(d1) + 1 '--go to beginning of d1
k = 0
'--get count of days in the month of d1
j = Day(DateAdd("m" , 1, d1) - Day(DateAdd("m" , 1, d1)))
For i = 0 To j
'--get count of times the d1 day occurs in the month of d1
If Weekday(d2 + i) = Weekday(d1) Then k = k + 1
If d2 + i > d1 Then Exit For
Next
DayOfMonthCount = k
End Function
'----------------------------------------------------

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Divit]

Re: NOT Particular Weekday in a Month but the opposite

Rich,
I may need a little bit of help. I've put the code in a module and from
the Visual basic Editor I can step through it. I've put in a msgbox to
see what the result is and it says that today is the second friday
(instead of thursday) but that has something to do with monday being my
first day and normal is sunday.

Now; when I open een a form I display the date, the weeknumber and in a
subform the actions that belong to this given date or day.

We have actions on every monday and actions for the first monday of the
month (and wednesdays and thursday to). So When I open the form I'll
have to caal the function to put in to a text box for example if this
is the second monday (or wednesday or thursday).

Can you provide me with extra hints!
Thanks so far

[kaniest]

Re: NOT Particular Weekday in a Month but the opposite

Rich P schreef:[color=blue]
> Function DayOfMonthCount (d1 As Date) As Integer[/color]

Using integer division operator "\", the code can be s
implified a little:

DayOfMonthCount = 1 + (Day(d1) - 1) \ 7

--
Paul

[Divit]

Re: NOT Particular Weekday in a Month but the opposite

Sometimes people whoe are an expert in things are very cryptic for
people who are reasonable new in this stuff.

Paul, I appreciate the help but I have no idea what you mean.

On opening my form I've got in a field "second thursday" and thats what
i wanted. Now the rest of this challenge!!

[kaniest]

Re: NOT Particular Weekday in a Month but the opposite

Divit[color=blue]
> On opening my form I've got in a field "second thursday" and thats what
> i wanted. Now the rest of this challenge!![/color]

My code only replaces Rich's version of the calculation.
I'm not shure what it is that your trying to do.
Maybe you want to know the exact date of the second
thursday in a certain month of a certain year?

[Divit]

Re: NOT Particular Weekday in a Month but the opposite

No, as I explained I want to know what "day" today is. It's april 13th
and more important it's the second thursday of the month. I want to
build an application to do my planning. We have actions that occur
every monday for example but also actions in even-weeks on wednesday.

So I have to determine what "day" todat is.

I solved the "second thursday of april" problem and ofcourse the day
and the weeknumbers where no problem. Now I have to figure how to
combine my 2 tables. 1 with actions and the other with occurences.

Hope you got it.
Bart

[kaniest]

Re: NOT Particular Weekday in a Month but the opposite

Divit schreef:[color=blue]
> No, as I explained I want to know what "day" today is. It's april 13th
> and more important it's the second thursday of the month. I want to
> build an application to do my planning. We have actions that occur
> every monday for example but also actions in even-weeks on wednesday.
>
> So I have to determine what "day" todat is.
>
> I solved the "second thursday of april" problem and ofcourse the day
> and the weeknumbers where no problem. Now I have to figure how to
> combine my 2 tables. 1 with actions and the other with occurences.
>
> Hope you got it.[/color]

Well ... let me rephrase.

One table specifies for each action a pattern of calendar dates -
monday of an even week, 2nd thursday of a month, etc.

Given a date range FromDate..ToDat e you can derive a
planning of actions on actual dates in that range.
That's what your after, or?

What 2nd table do you mean? Some technical solutions
use a table of all dates in the range. Is that the one?

[Lyle Fairfield]

Re: NOT Particular Weekday in a Month but the opposite

A little fun function for morning coffee.

Public Function Whatever(ByVal d As Date) As String
Dim a As Variant
a = Array("1st", "2nd", "3rd", "4th", "5th", "6th", "7th")
Whatever = a((Day(d) - 1) \ 7) & " " & WeekdayName(Wee kday(d),
True, vbSunday)
End Function

Works for April 2006. Not tested otherwise.
Done in Access 2003. Not sure in what version the WeekdayName function
was given to us. Second Parameter ("True") of WeekdayName function
determines abbreviations or full name.

[Divit]

Re: NOT Particular Weekday in a Month but the opposite

1 table has got the actions (name, day, special)
1 table has got the day-items
1 table has got the special

So
Action A occurs EVERY MONDAY
Action B occurs EVERY FIRST WEDNESDAY OF THE MONTH
Action C occurs EVERY THURSDAY IN EVEN WEEKS

So in the form I can determine;
1. what day(name) it is
2. is it the first/decond/third/fourth XXX-day of the month
3. is it an even weeknumber

pffft...hard to explain when your not a wizard and english isn't your
native language

[kaniest]

Re: NOT Particular Weekday in a Month but the opposite

Divit:[color=blue]
> pffft...hard to explain when your not a wizard and english isn't your
> native language[/color]

Same problems here, I'm lost ...

[Divit]

Re: NOT Particular Weekday in a Month but the opposite

In het Nederlands dan maar?

Ik wil een planning maken in Access. Dus ik heb een tabel waar de
acties in staan, in een adere table staan de dagnamen en in de derde
tabel de specifieke dag acties (derde donderdag van de maand, maandag
in even weken) dus die tabellen heb ik gekoopeld.

Dus heb ik nu een filter op een formulier naar een subformulier. Als ik
dat open wordt er gefilterd op de dagnaam, gaat allemaal prima.

Ik moest weten welke donderdag van de maand het was en dat heb ik dus
nu. Eigenlijk ben ik al een stuk verder.
Als het donderdag is zie ik alle acties van de donderdag ook als het
alleen op de 2de donderdag van de maand moet. Het zou leuk zijn als ik
de actie die op de derde donderdag van de maand uitgevoerd moet worden
ook alleen maar zie als het de derde donderdag is. Probleem daarbij is
natuurlijk dat als hemelvaart op de derde donderdag valt ik die actie
niet zie en ik hem ook niet uitvoer.......

Beetje duidelijker?

prettige paasdagen/ Happy Easter!

[kaniest]

Re: NOT Particular Weekday in a Month but the opposite

Divit:[color=blue]
> In het Nederlands dan maar?[/color]

Let't try it in english - a good exercise for both of us.
[color=blue]
> Ik wil een planning maken in Access. Dus ik heb een tabel waar de
> acties in staan, in een adere table staan de dagnamen en in de derde
> tabel de specifieke dag acties (derde donderdag van de maand, maandag
> in even weken) dus die tabellen heb ik gekoopeld.[/color]

If I read you correctly, you have:
table Actions(action, ...)
table Daynames(weekda ynumber, weekdayname)
table Chrontab(action , datepattern)

Note: Format(dtm,"ddd d") returns de weekday name in your locale.
So tables Daynames is probably not required.

prettige paaseieren,
Paul

[Rich P]

Re: NOT Particular Weekday in a Month but the opposite

very slick morphing

Public Function Whatever(ByVal d As Date) As String
Dim a As Variant
a = Array("1st", "2nd", "3rd", "4th", "5th", "6th", "7th")
Whatever = a((Day(d) - 1) \ 7) & " " & WeekdayName(Wee kday(d),
True, vbSunday)
End Function

I was drinking my morning Avian when I wrote my version.

Rich

*** Sent via Developersdex http://www.developersdex.com ***