435,165 Members | 840 Online
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
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.