Automatically update all future dates when earlier date is entered

[Dave B]

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

 

[Jerry Whittle]

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?

 

[KARL DEWEY]

I think the other dates are 'spreadsheet' dates.

Are all three dates in the same record?

 

[Ken Sheridan]

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