get just month and year from week number?

[broj1]

I have numbers 1-52 in combobox
when number selected i would like in other textbox to return month & (system)year

I have tried lots of variation with datediff, dateadd, and on forums i can find just to return specific date with week day also...but i don't need that.
any idea?
thx

 

[dhookom]

What would you expect when a week spans two months? You should be able to use an expression that adds the number of weeks to DateSerial(Year(Date()),1,1).

However it isn't clear what you want in the text box. You state month and year but don't provide any format. How about some examples and how you would handle weeks that straddle a month end.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[broj1]

thx for your reply.

you have a point there. i didn't think about that.


Function DateFromWeekNo(YearNo As Integer, weekno As Integer)
Dim datDate As Date
Dim temp As Date

If IsMissing(YearNo) Then YearNo = Year(date)
datDate = DateSerial(YearNo, 1, 1) + (weekno - 1) * 7
temp = datDate - Weekday(datDate, vbUseSystemDayOfWeek) + 1
DateFromWeekNo = Format(temp, "myyyy")
End Function

I used this function and it is working ok.
But i am missing something.....don't know what yet.

 

[dhookom]

What are you doing with the "other textbox to return month & (system)year"? Why do you need it?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[broj1]

It is like this;
i have a combo with value list 1-52 - represent the weekno.
when user selects it in a textbox i need to return a month and a current year for this. couse i need to pull and sum the data (to check free capacty ) for choosen week and month.

So far all looks ok. The problem is in report where i would like to show data ordered:
first group by month then by week, like;

february
week 5 -- 45
week 6 -- 78
week 7 -- 55
week 8 -- 62
total 240
March
week ...
...
..
and so on for whole year

Suming for month is no problem (sum all quantities with same month)
but weeks: first and last week in every month is shared with previous or next months week ... so, week 8 in this case half is in february half is in march.
The question is: how to transfer this so i will get the right numbers?
hope it is clear what is i am looking for.
thx

 

[dhookom]

I am still confused (happens often). Are you attempting to use the combo box as a criteria or is it bound to a field in a table or what?

Can you back way up and tell us what you are attempting to accomplish?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[TheAceMan1]

How are ya broj1 . . .

[blue]dhookom's[/blue] post origination still holds true! [purple]Month crossover[/purple] is still a problem unless some specific rules are laid down.

The 1st day of the week can occur on the last day of a month, as well as the last day of a week occuring on the first day of a month . . . . [red]Is this Legit?[/red] . . . or simply acceptable?

[blue]Either way, those weeks with problem will be ambiguous in their presentation![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[broj1]

thx for your replies ...you are great people!

i've gone behind the back into a pocket. so i split into to sections weeks-with same weekNo and months and then sum each.
well it did the job.