Date calculation

  • Thread starter Thread starter Ioia
  • Start date Start date
I

Ioia

I'm new at Access. I have two date fields in a form: First contact and
Removal Date. I need to calculate automatically the date for Removal Date, 8
weeks ending Friday from the First Contact Date. Any suggestions would be
much appreciate.
Thank you
Ioia
 
You can use an expression on the form. If the Removal date is ALWAYS 8 weeks
and you never change it then you should always calculate it and never store it.

To get Friday of a week that is eight weeks later you would use this expression.
DateAdd("d",62-Weekday([FirstContact],6),[FirstContact])

You can use that expression in a query whenever you need the date. You can
use that expression as the source of a control on a report or in a form with
an equals sign in front of the expression.

If you really feel you need the value to be stored you will need to add VBA
code to the after update event of the FirstContact control.

If IsDate(Me.TxtFirstContactControl) then
Me.txtRemovalDateControl=DateAdd("d",62-Weekday([FirstContact],6),[FirstContact])
End If


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Hi John,
Thank you for your reply, but I think I did not make myself clear. What I
ment was to
get a Removal Date always on a Friday in 8 weeks time. When I write your
expression the removal date is not always a Friday, and I need it to be a
Friday.
I do not need the date store in a table as long as I can use it in forms and
reports.
Thanks a lot
ioia

John Spencer said:
You can use an expression on the form. If the Removal date is ALWAYS 8 weeks
and you never change it then you should always calculate it and never store it.

To get Friday of a week that is eight weeks later you would use this expression.
DateAdd("d",62-Weekday([FirstContact],6),[FirstContact])

You can use that expression in a query whenever you need the date. You can
use that expression as the source of a control on a report or in a form with
an equals sign in front of the expression.

If you really feel you need the value to be stored you will need to add VBA
code to the after update event of the FirstContact control.

If IsDate(Me.TxtFirstContactControl) then
Me.txtRemovalDateControl=DateAdd("d",62-Weekday([FirstContact],6),[FirstContact])
End If


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I'm new at Access. I have two date fields in a form: First contact and
Removal Date. I need to calculate automatically the date for Removal Date, 8
weeks ending Friday from the First Contact Date. Any suggestions would be
much appreciate.
Thank you
Ioia
Click to expand...
.
Click to expand...
 
My error. Try the following
DateAdd("d",63-Weekday(FirstContact,7),FirstContact)

I should have been setting SATURDAY (7) as the first day of the week and not
FRIDAY (6) in the Weekday argument. Friday is the last day of the week for
the purposes of this calculation.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you very much! It works
Ioia

John Spencer said:
My error. Try the following
DateAdd("d",63-Weekday(FirstContact,7),FirstContact)

I should have been setting SATURDAY (7) as the first day of the week and not
FRIDAY (6) in the Weekday argument. Friday is the last day of the week for
the purposes of this calculation.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

.
Click to expand...
 
Back
Top