weeknumber should return a date

[Jean-Paul]

I have a field that contains a weeknumber

Upon opening the form I wrote:
Me!weeknummer = DatePart("ww", Date)

I have 2 puchbuttons "+" and "-" than increase and decrease the number.
There are also 6 fields on the form
What I want is:
When I choose weeknumber 2 the correct dates should be entered in each
of the 6 fields, starting with monday as the first date

example:
Weeknumer = 2

field monday= 01/05/09
field thuesday = 01/05/09
.....

if weeknumber = 1

field monday= 12/29/08
field thuesday = 12/30/08
.....

any idea?
Thanks

 

[Graham Mandeno]

Hi Jean-Paul

Add another hidden textbox to your form, named txtMonday1. Then add to your
opening code:
Me!txtMonday1 =
DateSerial(Year(Date),1,1)-WeekDay(DateSerial(Year(Date),1,1))+vbMonday

This will calculate the date of the Monday in Week 1.

Now, set the ControlSource of your Monday textbox to:
=DateAdd( "ww", [Weeknumber]-1, [txtMonday1] )

This will add the number of weeks (less one) to the first Monday.

Finally, set the ControlSource for the other "day" textboxes as follows:
Tuesday: =[Monday]+1
Wednesday: =[Monday]+2
.... etc

 

[ND Pard]

Let’s call your desired date “YourDateâ€.

Then the value of the field for:
Sunday =YourDate + Choose((YourDate Mod 7) + 1, 1, 0, -1, -2, -3, -4, -5)
Monday =YourDate + Choose((YourDate Mod 7) + 1, -5, 1, 0, -1, -2, -3, -4)
Tuesday =YourDate + Choose((YourDate Mod 7) + 1, -4, -5, 1, 0, -1, -2, -3)
Wednesday =YourDate + Choose((YourDate Mod 7) + 1, -3, -4, -5, 1, 0, -1, -2)
Thursday =YourDate + Choose((YourDate Mod 7) + 1, -2, -3, -4, -5, 1, 0, -1)
Friday =YourDate + Choose((YourDate Mod 7) + 1, -1, -2, -3, -4, -5, 1, 0)
Saturday =YourDate + Choose((YourDate Mod 7) + 1, 0, -1, -2, -3, -4, -5, 1)

I hope this helps.

Good Luck.

Hi Jean-Paul

Add another hidden textbox to your form, named txtMonday1. Then add to your
opening code:
Me!txtMonday1 =
DateSerial(Year(Date),1,1)-WeekDay(DateSerial(Year(Date),1,1))+vbMonday

This will calculate the date of the Monday in Week 1.

Now, set the ControlSource of your Monday textbox to:
=DateAdd( "ww", [Weeknumber]-1, [txtMonday1] )

This will add the number of weeks (less one) to the first Monday.

Finally, set the ControlSource for the other "day" textboxes as follows:
Tuesday: =[Monday]+1
Wednesday: =[Monday]+2
.... etc
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a field that contains a weeknumber

Upon opening the form I wrote:
Me!weeknummer = DatePart("ww", Date)

I have 2 puchbuttons "+" and "-" than increase and decrease the number.
There are also 6 fields on the form
What I want is:
When I choose weeknumber 2 the correct dates should be entered in each of
the 6 fields, starting with monday as the first date

example:
Weeknumer = 2

field monday= 01/05/09
field thuesday = 01/05/09
....

if weeknumber = 1

field monday= 12/29/08
field thuesday = 12/30/08
....

any idea?
Thanks

 

[ND Pard]

Mea Culpa ... I've got to quick trying to provide an answer on days I'm hung
over

Hi Jean-Paul

Add another hidden textbox to your form, named txtMonday1. Then add to your
opening code:
Me!txtMonday1 =
DateSerial(Year(Date),1,1)-WeekDay(DateSerial(Year(Date),1,1))+vbMonday

This will calculate the date of the Monday in Week 1.

Now, set the ControlSource of your Monday textbox to:
=DateAdd( "ww", [Weeknumber]-1, [txtMonday1] )

This will add the number of weeks (less one) to the first Monday.

Finally, set the ControlSource for the other "day" textboxes as follows:
Tuesday: =[Monday]+1
Wednesday: =[Monday]+2
.... etc
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a field that contains a weeknumber

Upon opening the form I wrote:
Me!weeknummer = DatePart("ww", Date)

I have 2 puchbuttons "+" and "-" than increase and decrease the number.
There are also 6 fields on the form
What I want is:
When I choose weeknumber 2 the correct dates should be entered in each of
the 6 fields, starting with monday as the first date

example:
Weeknumer = 2

field monday= 01/05/09
field thuesday = 01/05/09
....

if weeknumber = 1

field monday= 12/29/08
field thuesday = 12/30/08
....

any idea?
Thanks

 

[Jean-Paul]

it's OK... thanks anyway

Mea Culpa ... I've got to quick trying to provide an answer on days I'm hung
over

Hi Jean-Paul

Add another hidden textbox to your form, named txtMonday1. Then add to your
opening code:
Me!txtMonday1 =
DateSerial(Year(Date),1,1)-WeekDay(DateSerial(Year(Date),1,1))+vbMonday

This will calculate the date of the Monday in Week 1.

Now, set the ControlSource of your Monday textbox to:
=DateAdd( "ww", [Weeknumber]-1, [txtMonday1] )

This will add the number of weeks (less one) to the first Monday.

Finally, set the ControlSource for the other "day" textboxes as follows:
Tuesday: =[Monday]+1
Wednesday: =[Monday]+2
.... etc
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a field that contains a weeknumber

Upon opening the form I wrote:
Me!weeknummer = DatePart("ww", Date)

I have 2 puchbuttons "+" and "-" than increase and decrease the number.
There are also 6 fields on the form
What I want is:
When I choose weeknumber 2 the correct dates should be entered in each of
the 6 fields, starting with monday as the first date

example:
Weeknumer = 2

field monday= 01/05/09
field thuesday = 01/05/09
....

if weeknumber = 1

field monday= 12/29/08
field thuesday = 12/30/08
....

any idea?
Thanks

 

[Jean-Paul]

remark though...

It would be great to get the date of the monday of the current week in
the txtMonday1 textbox (I think this is the most logical way, or am I
wrong?)

So, if I open the form today (01/14/09) the data in the filed should be
01/12/09

or

opening on 01/20/09 it should read 01/19/09

or

opening on 01/26/09 it should read 01/26/09

Is that possible?

Thanks again

 

[Graham Mandeno]

Well, you're very welcome! ;-)

The date of the Monday of the "current" week is dictated by the selected
week number. This calculation is based on the date of the Monday in week 1,
which is constant (for any given year). IMHO it makes more sense to
calculate this "constant" once (when the form opens) than to include that
calculation in the offset calculation of the date of the Monday in week N.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

remark though...

It would be great to get the date of the monday of the current week in the
txtMonday1 textbox (I think this is the most logical way, or am I wrong?)

So, if I open the form today (01/14/09) the data in the filed should be
01/12/09

or

opening on 01/20/09 it should read 01/19/09

or

opening on 01/26/09 it should read 01/26/09

Is that possible?

Thanks again

Hi Jean-Paul

Add another hidden textbox to your form, named txtMonday1. Then add to
your opening code:
Me!txtMonday1 =
DateSerial(Year(Date),1,1)-WeekDay(DateSerial(Year(Date),1,1))+vbMonday

This will calculate the date of the Monday in Week 1.

Now, set the ControlSource of your Monday textbox to:
=DateAdd( "ww", [Weeknumber]-1, [txtMonday1] )

This will add the number of weeks (less one) to the first Monday.

Finally, set the ControlSource for the other "day" textboxes as follows:
Tuesday: =[Monday]+1
Wednesday: =[Monday]+2
... etc

 

[ND Pard]

Here are the formulas I meant to give you earlier … only these work

Let’s call your desired date “YourDateâ€.

Then the value of the field for:
Sunday =YourDate+CHOOSE(MOD(YourDate,7)+1,-6,0,-1,-2,-3,-4,-5)
Monday =YourDate+CHOOSE(MOD(YourDate,7)+1,-5,1,0,-1,-2,-3,-4)
Tuesday =YourDate+CHOOSE(MOD(YourDate,7)+1,-4,2,1,0,-1,-2,-3)
Wednesday =YourDate+CHOOSE(MOD(YourDate,7)+1,-3,3,2,1,0,-1,-2)
Thursday =YourDate+CHOOSE(MOD(YourDate,7)+1,-2,4,3,2,1,0,-1)
Friday =YourDate+CHOOSE(MOD(YourDate,7)+1,-1,5,4,3,2,1,0)
Saturday =YourDate+CHOOSE(MOD(YourDate,7)+1,0,6,5,4,3,2,1)

I do however, agree with Jean-Paul suggestion.
That is, for simplification, calculate only one of the dates and then
add or subtract from there to arrive at all remaining dates.

I hope this helps.

Good Luck.

 

[ND Pard]

oops ... the above code is for Excel. The following is for Access.

Here are the formulas I meant to give you earlier … only these work

Let’s call your desired date “YourDateâ€.

Then the value of the field for:
Sunday =YourDate+CHOOSE((YourDate Mod 7)+1,-6,0,-1,-2,-3,-4,-5)
Monday =YourDate+CHOOSE((YourDate Mod 7)+1,-5,1,0,-1,-2,-3,-4)
Tuesday =YourDate+CHOOSE((YourDate Mod 7)+1,-4,2,1,0,-1,-2,-3)
Wednesday =YourDate+CHOOSE((YourDate Mod 7)+1,-3,3,2,1,0,-1,-2)
Thursday =YourDate+CHOOSE((YourDate Mod 7)+1,-2,4,3,2,1,0,-1)
Friday =YourDate+CHOOSE((YourDate Mod 7)+1,-1,5,4,3,2,1,0)
Saturday =YourDate+CHOOSE((YourDate Mod 7)+1,0,6,5,4,3,2,1)

I do however, agree with Jean-Paul suggestion.
That is, for simplification, calculate only one of the dates and then
add or subtract from there to arrive at all remaining dates.

I hope this helps.

Good Luck.