Update Query

How do I create an update query to automatically assign the create date of
9/24/2009 as the end date for for the record with the create date of 9/15/09.

ID CATEGORY DURATION CREATE_DATE END_DATE
2381 Service 13 - 24 months 9/15/2009 9:35:00 AM
2381 Service 13 - 24 months 9/24/2009 12:41:00 PM

The SQL for what you asked would look like the following.

UPDATE SomeTable
Set End_Date = #2009-09-24#
WHERE Create_Date =#2009-09-15# and Create_Date #2009-09-16#
AND End_Date is Null

However, what I think you are asking is how to do this for a group of records
where End_Date is Null

UPDATE SomeTable
SET End_Date = DMin("Create_Date","SomeTable", "ID=" & ID & " AND
Create_Date" & Format(Create_Date,"\#yyyy-mm-dd\#"))
WHERE End_Date is Null

In query design view
== add your table
== add the end_date field
== set the criteria to Is Null
== Select Query Update
== Enter the following (adjust for your table name) into the update to
DMin("Create_Date","[SomeTable]", "ID=" & ID & " AND Create_Date " &
Format(Create_Date,"\#yyyy-mm-dd\#"))

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

Access Rookie wrote:
How do I create an update query to automatically assign the create date of
9/24/2009 as the end date for for the record with the create date of 9/15/09.

ID CATEGORY DURATION CREATE_DATE END_DATE
2381 Service 13 - 24 months 9/15/2009 9:35:00 AM
2381 Service 13 - 24 months 9/24/2009 12:41:00 PM