week(date) function etal.

[Noel]

Two questions....

1) Is there a week(date) function that tells you what week the date falls in? E.g. week(1/1/07) would return 1.
and week(12/31/07) would return 52 or something like that.

2) For a calendar style equipment scheduling/tracking application where the max number of equipment changes weekly, I ultimately want to compare a max value of available equipment to the count that was entered/scheduled for the equipment and if the entered value exceeds the max value, I want the application to shout.

The approach I am thinking of taking is to have a x-ref table that has weeks 1 - 52 and the max number of equipment available that would be joined in a query to the equipment schedule data.

In the application, I want to look at the date that the equipment was scheduled for, determine the week (1-52) and then compare the count of scheduled equipment to the max value in the x-ref table. If the count exceeds the max then "shout".

Does that seem like a sound plan? any alternate ideas?

Thanks.
Noel

 

[RuralGuy]

Look up Format(Date(),"ww")

 

[Ryko]

Remember, however, using Format(Date(),"ww), same week number is assigned Sunday through Saturday. So, if 1/1/year is Saturday then Format(1/1/year, "ww") = 1, but Format(2/1/year,"ww") = 2. If your x-ref table specifies week number in a different way you'll probably need a different approach. Also you will need to include year number when working with data over several years.

 

[Noel]

here's what i ended up with ... that seems to work

Thanks for your responses.

Since I set up the x-ref table for just weeks 1-52, then after the join in the query, I'm left with just the current year's data...which I suppose I need to think on some more if I need to go beyond.

Anyway, thanks again.
======
Function Week(d)
Dim Y
Dim Jan1

Y = year(Now())
Jan1 = DateValue("1/1/" & Y)
d = DateValue(Format(d, "mm/dd/yyyy"))
Week = DateDiff("ww", Jan1, DateValue(d)) + 1

End Function

 

[Noel]

P.S. I like you all's suggestion better

Function Week(d)

Week = Format(DateValue(d), "ww")

End Function