weeknumber should return a date

  • Thread starter Thread starter Jean-Paul
  • Start date Start date
J

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
 
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
 
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.


Graham Mandeno said:
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

Jean-Paul said:
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
Click to expand...
Click to expand...
 
Mea Culpa ... I've got to quick trying to provide an answer on days I'm hung
over :)

Graham Mandeno said:
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

Jean-Paul said:
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
Click to expand...
Click to expand...
 
it's OK... thanks anyway

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

Graham Mandeno said:
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

Jean-Paul said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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


Jean-Paul said:
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 said:
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
Click to expand...
Click to expand...
 
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.
 
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.
 
Back
Top