Week of Month function? 3

[Vidar13]

I need a function that will allow me to correspond any given date to the week number it follows. For instance, July 2, 2001 falls on a Monday and is technically week 2 (week 1 only had one day in it - assuming Monday is first day of week and Sunday is last).

I need this for a sales comparison by same day and week number for a prior year. I already know the DayOfWeek function for determining what day of the week a date falls on.. now I need one that tells me the week.

Any ideas?

 

[Philly44]

I'm not sure where your defining things but I know that in Query design view if you specify the field as follows it will give the week number to and store in the WeedGroup variable

WeekGroup: DatePart("ww",[DateField])

I hope this helps, if you look up the DatePart function it tells you how to extract ust about anything from the date

 

[jebry]

Hi!

Note that Access assumes that Sunday is the first day of the week. I noticed in your post that you want Monday to be considered the first day. To do this use:

DatePart("ww",[DateField],vbMonday)

hth
Jeff Bridgham

 

[Databaseguy]

You guys are close. DatePart("ww",now(),vbMonday), will return 32, not 2, which is what I beleive Vidar13 is looking for.

This is somethng I could use also. I'll post if I find something.

 

[Vidar13]

There's a problem with this: The "ww" part of the DatePart function, gives you the week number starting from the beginning of the declared year. I need the week number from the first of that particular month.

There's also another hitch. I'm trying to do this within a query and it won't take the vbMonday parameter (obviously because it's a VBA parameter).

 

[jebry]

Hi again!

Sorry that was a no brainer! Try this:

Int((Day(Date) + (7 - WeekDay(Date))) / 7) + 1

There's probably something easier but if not this should work.

hth
Jeff Bridgham

 

[Vidar13]

Okay, that would work if the first day of the week was Sunday. Unfortunately, that's not the case.

I was able to make a formula through VBA code to get the results I'm after, but unfortunately, the catch again, is the first day of the week parameter.

What I did was extract the difference between the week number of the date in question and the week number of the first day of that same month (and then added one to get the proper count):

DatePart("ww", DateField, vbMonday) - DatePart("ww", Month(DateField) & "/1/" & Year(DateField), vbMonday) + 1

What can I do about the vbMonday parameter not being accepted in a query?

 

[Vidar13]

Nevermind, Jebry had it right, I just needed one tweak to it:

Int((Day([Date])+(7-Weekday([Date]-1)))/7)+1

Notice the "-1".. that shifts the date it's looking at by one day.

Thanks, guys! This works awesome!

 

[Vidar13]

I spoke too soon. The above code works as long as the first day of the month doesn't fall on a Monday. January 1, 2001, for instance, fell on a Monday and the results of the above code return week #2 for 01/01/2001. There must be a way of reworking the above code, but it's not coming to me.

 

[jebry]

Try using the vbMonday in the Weekday function i.e.

Int((Day(Date) + (7 - WeekDay(Date, vbMonday))) / 7) + 1

Let me know if that works.

Jeff Bridgham

 

[Vidar13]

The vbMonday parameter isn't acceptable in the weekday function within a query... only when used in VBA code.

 

[Vidar13]

It's ugly, but I was able to do the following to correct that bug:

IIf(Weekday(Month([Date]) & "/1/" & Year([DailyDate]))=2,Int((Day([Date])+(7-Weekday([Date]-1)))/7),Int((Day([Date])+(7-Weekday([Date]-1)))/7)+1)

Now, if no other bugs pop up, this should be good to go.

 

[Craig0201]

Create this public function and the world is your oyster!

Public Function WeekInMonth(dte As Date) As Integer

Dim strMonthYear As String
Dim dteFirstMonth As Date
Dim intDaysDiff As Integer
Dim intDayFirstOfMonth As Integer

strMonthYear = Month(dte) & "/" & Year(dte)
dteFirstMonth = "01" & "/" & strMonthYear

intDayFirstOfMonth = WeekDay(dteFirstMonth, vbMonday)

intDaysDiff = (dte - dteFirstMonth) + intDayFirstOfMonth - 1

WeekInMonth = Int(intDaysDiff / 7) + 1

End Function

HTH

Craig

 

[jebry]

Good Job Craig!

I also was going to recommend a public function!

Jeff Bridgham