Changing result of a calculated date

[Alanclen]

I have 2 fields StartDate & Period(whole Weeks) resulting in a 3rd field -
end date (calculated by [StartDate]+([Period]*7)-1). However, on occasion I
need to change the end date by a day or two. I also need to know that the
date has been changed ie by changing font colour to red if changed. Can
anyone help please.

 

[BruceM]

First, DateAdd (see Help for more information) could be used to calculate
the EndDate. For instance, in the After Update event of the Period text
box:
Me.EndDate = DateAdd("ww",[Period]-1,[StartDate])
Click into the EndDate text box, then click Format > Conditional Formatting
on the menu bar. Set "Field value is","Not equal to" (in the first two
dialog boxes), then put the expression into the third (rightmost) dialog
box:
DateAdd("ww",[Period]-1,[StartDate])
Set the font color to red (but be aware that unless the font if fairly large
and/or bold the formatting may be lost on those of us who have a color
deficiency in our vision (aka color blindness). Red/green dificiency, in
particular, is more common than you may realize. I can't begin to tell you
how often somebody has used such formatting to make something obvious, but I
never notice it.

 

[Alanclen]

Thanks for that. The only thing not quite right is that the "minus 1" is one
day not week. In other words if the period is for 2 weeks starting on 1st
April the result is 15th April whereas I require 14th April ie 14 days
inclusive.

First, DateAdd (see Help for more information) could be used to calculate
the EndDate. For instance, in the After Update event of the Period text
box:
Me.EndDate = DateAdd("ww",[Period]-1,[StartDate])
Click into the EndDate text box, then click Format > Conditional Formatting
on the menu bar. Set "Field value is","Not equal to" (in the first two
dialog boxes), then put the expression into the third (rightmost) dialog
box:
DateAdd("ww",[Period]-1,[StartDate])
Set the font color to red (but be aware that unless the font if fairly large
and/or bold the formatting may be lost on those of us who have a color
deficiency in our vision (aka color blindness). Red/green dificiency, in
particular, is more common than you may realize. I can't begin to tell you
how often somebody has used such formatting to make something obvious, but I
never notice it.

I have 2 fields StartDate & Period(whole Weeks) resulting in a 3rd field -
end date (calculated by [StartDate]+([Period]*7)-1). However, on occasion
I
need to change the end date by a day or two. I also need to know that the
date has been changed ie by changing font colour to red if changed. Can
anyone help please.

 

[BruceM]

Try:

Me.EndDate = DateAdd("d",([Period]*7)-1,[StartDate])

This is pretty much what you had, except that I suggested using DateAdd.
Because of how Access stores dates you could have done this without DateAdd.
If the reason you need to change the date sometimes is that the formula
leaves Sunday or Saturday as EndDate, it is possible to automate the process
rather than manually changing the date, but before I can suggest anything I
would need to know the business rule behind selecting an alternate date.

Thanks for that. The only thing not quite right is that the "minus 1" is
one
day not week. In other words if the period is for 2 weeks starting on 1st
April the result is 15th April whereas I require 14th April ie 14 days
inclusive.

First, DateAdd (see Help for more information) could be used to calculate
the EndDate. For instance, in the After Update event of the Period text
box:
Me.EndDate = DateAdd("ww",[Period]-1,[StartDate])
Click into the EndDate text box, then click Format > Conditional
Formatting
on the menu bar. Set "Field value is","Not equal to" (in the first two
dialog boxes), then put the expression into the third (rightmost) dialog
box:
DateAdd("ww",[Period]-1,[StartDate])
Set the font color to red (but be aware that unless the font if fairly
large
and/or bold the formatting may be lost on those of us who have a color
deficiency in our vision (aka color blindness). Red/green dificiency, in
particular, is more common than you may realize. I can't begin to tell
you
how often somebody has used such formatting to make something obvious,
but I
never notice it.

I have 2 fields StartDate & Period(whole Weeks) resulting in a 3rd
field -
end date (calculated by [StartDate]+([Period]*7)-1). However, on
occasion
I
need to change the end date by a day or two. I also need to know that
the
date has been changed ie by changing font colour to red if changed. Can
anyone help please.