WeekNum Function?

[VBAWTB]

Does anyone know how to use the Excel WeekNum() function in Access?

 

[John Big Booty]

Try;

[URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("ww", [YourDateField])

 

[stopher]

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:

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:

Public Function myWeekNum(myDate As Date) As Integer
    myWeekNum = Excel.WorksheetFunction.WeekNum(myDate)
End Function

Then use it in your query like this:

myWeekNum(someDate)

hth
Chris

 

[VBAWTB]

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]

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

 

[Deleted member 147267]

Umm, you have answered a nine year old thread...

 

[plog]

Umm, you have answered a nine year old thread...

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'?

 

[Deleted member 147267]

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?

 

[Gasman]

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?

It says he joined Apr 28, 2011 though?

 

[Deleted member 147267]

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.

 

[Pat Hartman]

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.