weeknumber should return a 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
 
G

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
 
N

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.


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
 
N

ND Pard

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
 
J

Jean-Paul

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
 
J

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
 
G

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


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
 
N

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top