Prev: an ALIAS question?
Next: Combine Format function?
From: Access Rookie on 22 Mar 2010 03:10
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

From: John Spencer on 22 Mar 2010 08:24
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
>
 | 
Pages: 1
Prev: an ALIAS question?
Next: Combine Format function?