Calculate Weekending date based on timestamp

[Arvin]

Hello,

I am current designing a form the has textbox called "comments_timestamp" which is has a default value of "=Now()"

I have another textbox on the same form that is called "Weekending Date"

I would like to populate the "Weekending Date" textbox with the weekending date based on the "comments_timestamp" textbox.

Currently, I have tried to use the following in the "Weekending Date" textbox control source property.

=([comments_timestamp])+7-Weekday([comments_timestamp],7)

Based on the code above.....

The "comments_timestamp" textbox is populated with the 11/27/2010 and the "Weekending Date" textbox populates with 12/3/2010. When it should populate with 11/28/2010.

Side Note ...

the week begins Monday and ends on Sunday.....

Any suggestions ?

Thank you in advance

Arv

 

[gemma-the-husky]

there is probably an easier way but I would do this

function weekenddate (chkdate as date) as date
while weekday(chkdate)<>vbsunday 
    chkdate=chkdate+1
wend
weekenddate=chkdate
end function

 

[Arvin]

Thank you for the suggestion gemma-the-husky....

Actually ... I think I screwed up my 1st post ....

I should of stated that I want the default value to be the "Weekending Date"

I did resolve the 1st is however ...

Previous code

=([comments_timestamp])+7-Weekday([comments_timestamp],7)
New Code

=([comments_timestamp])+7-Weekday([comments_timestamp],2)

But now .... in order to save the in the table .... I have to remove this from the Control Source Property and set it as a default value ....

Any suggestion on that one

Thank you in advance

Arv

 

[gemma-the-husky]

you can't have a calculation as a default

you either

a) dont store it at all, as you can easily calculate it whenever you need it or
b) recalculate it whenever you set a comments_timestamp (use the after update event)

a) is "normal", and probably best.

 

[Arvin]

you can't have a calculation as a default

you either

a) dont store it at all, as you can easily calculate it whenever you need it or
b) recalculate it whenever you set a comments_timestamp (use the after update event)

a) is "normal", and probably best.

So ... if i use "B" ....

Then I would use the

function weekenddate (chkdate as date) as date
while weekday(chkdate)<>vbsunday 
    chkdate=chkdate+1
wend
weekenddate=chkdate
end function

as an Afterupdate event?

Is my assumption correct?

Thank you in advance ....

 

[gemma-the-husky]

well you need to caclulate the date, and then assign the date to the control/underlying field, representing the weekending date.