WeekNum Function? (1 Viewer)
- Thread starter VBAWTB
- Start date
John Big Booty
AWF VIP
- Local time
- Today, 22:29
- Joined
- Aug 29, 2005
- Messages
- 8,263
Try;
Code:
[URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("ww", [YourDateField])
stopher
AWF VIP
- Local time
- Today, 13:29
- Joined
- Feb 1, 2006
- Messages
- 2,395
I much prefer John's excellent solution particularly as there are more options for deciding how the week number should be calculated.
However, if you really want to you can add a reference to the Excel Object Library. Then in VBA you can use the Excel function like this:
Since you've posted in the queries forum then I assume you want to use WeekNum in a query. So you'd have to wrap the code above in a function like this:
Then use it in your query like this:
myWeekNum(someDate)
hth
Chris
However, if you really want to you can add a reference to the Excel Object Library. Then in VBA you can use the Excel function like this:
Code:
Excel.WorksheetFunction.WeekNum(someDate)Since you've posted in the queries forum then I assume you want to use WeekNum in a query. So you'd have to wrap the code above in a function like this:
Code:
Public Function myWeekNum(myDate As Date) As Integer
myWeekNum = Excel.WorksheetFunction.WeekNum(myDate)
End FunctionThen use it in your query like this:
myWeekNum(someDate)
hth
Chris
Thank you both for the advise! I chose to go with the Excel.WorksheetFunction.WeekNum(someDate). It works beautifully for what I need it for. I realize that I posted in the queries section...but it is in some VBA code that is being executed on an event procedure. Thanks again =]
Despaten R. Purba
New member
- Local time
- Today, 05:29
- Joined
- Apr 28, 2011
- Messages
- 1
Public Function weeknumero(X As Date)
Dim bofyear As Date
bofyear = DateSerial(Year(X), 1, 1)
weeknumero = (X - bofyear + Weekday(bofyear, vbMonday) - 1) / 7 + 1
End Function
Dim bofyear As Date
bofyear = DateSerial(Year(X), 1, 1)
weeknumero = (X - bofyear + Weekday(bofyear, vbMonday) - 1) / 7 + 1
End Function
On one hand you would think someone who has been a member of this forum for 9 years would know to look at the thread dates. Then again, this is his first post, so maybe he deserves a little slack.
What exactly is the story Despaten? Lose your password? Coma? Really, really really spotty internet connection? Or just particular about coding and spent the better part of the last decade crafting that answer before finally hitting 'Submit'?
Last edited:
Gasman
Enthusiastic Amateur
- Local time
- Today, 13:29
- Joined
- Sep 21, 2011
- Messages
- 14,311
- Local time
- Today, 08:29
- Joined
- Feb 19, 2002
- Messages
- 43,293
If you use a search engine, you can find two versions of the Access VBA functions. One by name which is pretty useless and the second by category which is much more useful for finding a function whose name you don't know.