Update End Date

A

Access Rookie

How can I automatically update the End Date to display the create date from a
record previously reported. For example the end date for the first record
listed below should be 9/24/2009 12:41:00 PM and the end date for the third
record listed below should be 8/21/2009 9:00:00 AM.


ID CATEGORY DURATION CREATE_DATE END_DATE
3426 Service 24 months 9/15/2009 9:35:00 AM
3426 Service 24 months 9/24/2009 12:41:00 PM
3426 Service 36 months 8/20/2009 12:23:00 PM
3426 Service 36 months 8/21/2009 9:00:00 AM
 
J

John Spencer

Answered in the earlier posting, although you seem to have added further
criteria to the question.

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\#") & " AND Duration=""" & [Duration] & """")

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

Access Rookie

Thank you for taking the time to answer my question. So far I have not been
able to get this query to work.

Microsoft Office Access can't update all the records in the update query.
Microsoft Office Access didn't update 41575 fields due to type conversion
failure.

John Spencer said:
Answered in the earlier posting, although you seem to have added further
criteria to the question.

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\#") & " AND Duration=""" & [Duration] & """")

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

Access said:
How can I automatically update the End Date to display the create date from a
record previously reported. For example the end date for the first record
listed below should be 9/24/2009 12:41:00 PM and the end date for the third
record listed below should be 8/21/2009 9:00:00 AM.


ID CATEGORY DURATION CREATE_DATE END_DATE
3426 Service 24 months 9/15/2009 9:35:00 AM
3426 Service 24 months 9/24/2009 12:41:00 PM
3426 Service 36 months 8/20/2009 12:23:00 PM
3426 Service 36 months 8/21/2009 9:00:00 AM
.
 
J

John Spencer

Type Conversion failure means you are trying to put the wrong type of data in
to the field. Is End_Date field a datetime field or is it a text field that
contains a date string.

The other possible problem is Create_Date not being a datetime field.


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

Access said:
Thank you for taking the time to answer my question. So far I have not been
able to get this query to work.

Microsoft Office Access can't update all the records in the update query.
Microsoft Office Access didn't update 41575 fields due to type conversion
failure.

John Spencer said:
Answered in the earlier posting, although you seem to have added further
criteria to the question.

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\#") & " AND Duration=""" & [Duration] & """")

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

Access said:
How can I automatically update the End Date to display the create date from a
record previously reported. For example the end date for the first record
listed below should be 9/24/2009 12:41:00 PM and the end date for the third
record listed below should be 8/21/2009 9:00:00 AM.


ID CATEGORY DURATION CREATE_DATE END_DATE
3426 Service 24 months 9/15/2009 9:35:00 AM
3426 Service 24 months 9/24/2009 12:41:00 PM
3426 Service 36 months 8/20/2009 12:23:00 PM
3426 Service 36 months 8/21/2009 9:00:00 AM
.
 
A

Access Rookie

Both end_date and create_date are date time fields.

Any ideas? Could it be because not all records have a corresponding
create_date? if so, how can I fix that?

John Spencer said:
Type Conversion failure means you are trying to put the wrong type of data in
to the field. Is End_Date field a datetime field or is it a text field that
contains a date string.

The other possible problem is Create_Date not being a datetime field.


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

Access said:
Thank you for taking the time to answer my question. So far I have not been
able to get this query to work.

Microsoft Office Access can't update all the records in the update query.
Microsoft Office Access didn't update 41575 fields due to type conversion
failure.

John Spencer said:
Answered in the earlier posting, although you seem to have added further
criteria to the question.

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\#") & " AND Duration=""" & [Duration] & """")

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

Access Rookie wrote:
How can I automatically update the End Date to display the create date from a
record previously reported. For example the end date for the first record
listed below should be 9/24/2009 12:41:00 PM and the end date for the third
record listed below should be 8/21/2009 9:00:00 AM.


ID CATEGORY DURATION CREATE_DATE END_DATE
3426 Service 24 months 9/15/2009 9:35:00 AM
3426 Service 24 months 9/24/2009 12:41:00 PM
3426 Service 36 months 8/20/2009 12:23:00 PM
3426 Service 36 months 8/21/2009 9:00:00 AM

.
.
 
J

John Spencer

If you mean that in some records you have a create_Date that is null then I
would try eliminating those records in the update query.

In query design view
== add your table
== add the end_date field
== set the criteria to Is Null
==++ Add the Create_DateField
==++ Set the criteria to IS NOT 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\#") & " AND Duration=""" & [Duration] & """")

Another option would be to change the update expression to handle Nulls as if
they were a date far in the future.

DMin("Create_Date","[SomeTable]", "ID=" & [ID] & " AND Create_Date > " &
Format(NZ([Create_Date],#2999-01-01#),"\#yyyy-mm-dd\#") & " AND Duration=""" &
[Duration] & """")

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

Access said:
Both end_date and create_date are date time fields.

Any ideas? Could it be because not all records have a corresponding
create_date? if so, how can I fix that?

John Spencer said:
Type Conversion failure means you are trying to put the wrong type of data in
to the field. Is End_Date field a datetime field or is it a text field that
contains a date string.

The other possible problem is Create_Date not being a datetime field.


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

Access said:
Thank you for taking the time to answer my question. So far I have not been
able to get this query to work.

Microsoft Office Access can't update all the records in the update query.
Microsoft Office Access didn't update 41575 fields due to type conversion
failure.

:

Answered in the earlier posting, although you seem to have added further
criteria to the question.

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\#") & " AND Duration=""" & [Duration] & """")

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

Access Rookie wrote:
How can I automatically update the End Date to display the create date from a
record previously reported. For example the end date for the first record
listed below should be 9/24/2009 12:41:00 PM and the end date for the third
record listed below should be 8/21/2009 9:00:00 AM.


ID CATEGORY DURATION CREATE_DATE END_DATE
3426 Service 24 months 9/15/2009 9:35:00 AM
3426 Service 24 months 9/24/2009 12:41:00 PM
3426 Service 36 months 8/20/2009 12:23:00 PM
3426 Service 36 months 8/21/2009 9:00:00 AM

.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top