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:
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 Function
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 =]
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'?
When I look at Despaten's avatar I see "new member" so you've lost me on the 9 year wait thing. He's not the Op either so I'm clueless, but what's new?
When I look at Despaten's avatar I see "new member" so you've lost me on the 9 year wait thing. He's not the Op either so I'm clueless, but what's new?
Ahh well, look at the post count. Suggests a lack of experience.
Still, let's not start picking on each other for such things, OK? I won't be able to withstand the onslaught.
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.