calculating a date field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a table that I need to calculate the enddate field. I inherited this
database, and it is only now that the users need to calculate the enddate.
And of course, they need the endate to be retroactive.

The table is called tblPatientDispos. It is used to determine where
someonbe is at a particular date. The fields in the tableare Venue ID (pk),
DispoID, Venue, StartDate, EndDate. The EndDate should be the date before
the the next StartDate for the same DispoID.

A typical example of this the following:

VenueID DispoID StartDate EndDate

1123 4444 03/15/1999 ?
1679 4444 07/23/2004

How do I translate this into an SQL statement?

Any assistance would be appreciated greatly.

Thanks in advance.
 
A select query would look like the following

SELECT VenueID, DispoID, StartDate
, DMin("StartDate","tblPatientDispos","DispoID = " & [DispoID] & " AND
StartDate > #" & [StartDate] & "#") as EndDate
FROM tblPatientDispos

An update query would look like
UPDATE tblPatientDispos
SET EndDate = DMin("StartDate","tblPatientDispos","DispoID = " & [DispoID] &
" AND StartDate > #" & [StartDate] & "#")

Do this on a COPY of your data to make sure it works as you wish.

IF DispoID is a text field then you will need to insert a ' character before
and after [DispoID] like
"DispoID = '" & [DispoID] & "' AND
 
Thank you. I tried both solutions, and each worked great. It is helpful to
see how one may look at a question with multiple solutions. Thank you again
for helping me to think through these steps. Now I am able to apply this to
some other logic.

Cheers,
fascal

John Spencer said:
A select query would look like the following

SELECT VenueID, DispoID, StartDate
, DMin("StartDate","tblPatientDispos","DispoID = " & [DispoID] & " AND
StartDate > #" & [StartDate] & "#") as EndDate
FROM tblPatientDispos

An update query would look like
UPDATE tblPatientDispos
SET EndDate = DMin("StartDate","tblPatientDispos","DispoID = " & [DispoID] &
" AND StartDate > #" & [StartDate] & "#")

Do this on a COPY of your data to make sure it works as you wish.

IF DispoID is a text field then you will need to insert a ' character before
and after [DispoID] like
"DispoID = '" & [DispoID] & "' AND

fascal said:
Hi,

I have a table that I need to calculate the enddate field. I inherited
this
database, and it is only now that the users need to calculate the enddate.
And of course, they need the endate to be retroactive.

The table is called tblPatientDispos. It is used to determine where
someonbe is at a particular date. The fields in the tableare Venue ID
(pk),
DispoID, Venue, StartDate, EndDate. The EndDate should be the date before
the the next StartDate for the same DispoID.

A typical example of this the following:

VenueID DispoID StartDate EndDate

1123 4444 03/15/1999 ?
1679 4444 07/23/2004

How do I translate this into an SQL statement?

Any assistance would be appreciated greatly.

Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top