I have created a database in Access 2003 which in main is a schedule. I need
to be able to automatically update future dates when a newer date is entered
or a future date is amended to an earlier date. For example three dates
02/11/09, 03/11/09 & 05/11/09 – the date 05/11/09 is amended to 01/11/09 the
other two dates automatically change to 03/11/09 & 04/11/09. Is this
possible? Thanks

Technically you shouldn't store any data in a table which is dependent on
another field in the table. In your case if you change one date, you need to
change other dates.

Ideally what you want is only one date in the record. Then for forms and
reports you can use a function such as DateAdd to display other dates that
are dependent upon the date in the table.

I was trying to figure out how to do this with the examples that you
displayed, but there doesn't seem to be a logical relation or business rule
that I can "do the math" with. I even looked at the dates as MM/DD/YY and
DD/MM/YY. How do you know what the values of the other dates need to be if
the third one is changed?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Dave B" wrote:

> I have created a database in Access 2003 which in main is a schedule. I need
> to be able to automatically update future dates when a newer date is entered
> or a future date is amended to an earlier date. For example three dates
> 02/11/09, 03/11/09 & 05/11/09 – the date 05/11/09 is amended to 01/11/09 the
> other two dates automatically change to 03/11/09 & 04/11/09. Is this
> possible? Thanks

I think the other dates are 'spreadsheet' dates.

Are all three dates in the same record?

"Jerry Whittle" wrote:

> Technically you shouldn't store any data in a table which is dependent on
> another field in the table. In your case if you change one date, you need to
> change other dates.
>
> Ideally what you want is only one date in the record. Then for forms and
> reports you can use a function such as DateAdd to display other dates that
> are dependent upon the date in the table.
>
> I was trying to figure out how to do this with the examples that you
> displayed, but there doesn't seem to be a logical relation or business rule
> that I can "do the math" with. I even looked at the dates as MM/DD/YY and
> DD/MM/YY. How do you know what the values of the other dates need to be if
> the third one is changed?
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Dave B" wrote:
>
> > I have created a database in Access 2003 which in main is a schedule. I need
> > to be able to automatically update future dates when a newer date is entered
> > or a future date is amended to an earlier date. For example three dates
> > 02/11/09, 03/11/09 & 05/11/09 – the date 05/11/09 is amended to 01/11/09 the
> > other two dates automatically change to 03/11/09 & 04/11/09. Is this
> > possible? Thanks

You haven't given us much to go on, but as an example lets assume this is a
table PatientApppointments with columns PatientID, ClinicID and
AppointmentDate, which will of course be the composite primary key of the
table. Lets assume that for any appointment date entered, either by
inserting a new row or updating an existing row, for a patient at a
particular clinic, if later appointment dates exist for that patient at that
clinic each of those appointment dates will be advanced by one month. The
code for this would be in the AfterUpdate event procedure of a form or
subform based on the PatientApppointments table:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE PatientApppointments " & _
"SET AppointmentDate = DATEADD("m",1,AppointmentDate) " & _
"WHERE PatientID = " & Me.PatientID " AND ClinicID = "
& Me.ClinicID & " AND AppointmentDate > #" & _
& FORMAT(me.AppointmentDate, "yyyy-mm-dd") & "#"

cmd.CommandText = strSQL
cmd.Execute

Ken Sheridan
Stafford, England

"Dave B" wrote:

> I have created a database in Access 2003 which in main is a schedule. I need
> to be able to automatically update future dates when a newer date is entered
> or a future date is amended to an earlier date. For example three dates
> 02/11/09, 03/11/09 & 05/11/09 – the date 05/11/09 is amended to 01/11/09 the
> other two dates automatically change to 03/11/09 & 04/11/09. Is this
> possible? Thanks