By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

NOT Particular Weekday in a Month but the opposite

P: n/a
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

Apr 12 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
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.

Apr 12 '06 #2

P: n/a
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(DayOfMonthCount(d1)) & " " & _
WeekdayName(Weekday(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 ***
Apr 12 '06 #3

P: n/a
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

Apr 13 '06 #4

P: n/a
Rich P schreef:
Function DayOfMonthCount(d1 As Date) As Integer


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

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

--
Paul
Apr 13 '06 #5

P: n/a
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!!

Apr 13 '06 #6

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


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?
Apr 13 '06 #7

P: n/a
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

Apr 13 '06 #8

P: n/a
Divit schreef:
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.


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..ToDate 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?
Apr 13 '06 #9

P: n/a
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(Weekday(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.

Apr 13 '06 #10

P: n/a
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

Apr 13 '06 #11

P: n/a
Divit:
pffft...hard to explain when your not a wizard and english isn't your
native language


Same problems here, I'm lost ...
Apr 13 '06 #12

P: n/a
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!

Apr 13 '06 #13

P: n/a
Divit:
In het Nederlands dan maar?
Let't try it in english - a good exercise for both of us.
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.


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

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

prettige paaseieren,
Paul
Apr 13 '06 #14

P: n/a
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(Weekday(d),
True, vbSunday)
End Function

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

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Apr 14 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.