Update Date in Table

L

Lamar

I want to update the Year in a field in a table. Users kept entering 2006
instead of 2007. So I have a large number data entry errors where the
receive date is entered as 1/2/2006 when it should be 1/2/2007.

So is it possible to updte the year (2006 to 2007) without updating the
month and day?

Thank you for any help.
 
R

Roland Hall

Lamar said:
I want to update the Year in a field in a table. Users kept entering 2006
instead of 2007. So I have a large number data entry errors where the
receive date is entered as 1/2/2006 when it should be 1/2/2007.

So is it possible to updte the year (2006 to 2007) without updating the
month and day?
Click to expand...

Are there any records where you want to keep the year at 2006?
Are you doing this in Access or server-side script (ASP, PHP) etc.?
 
L

Lamar

I am doing this in MS Access 2003. Yes, there are records I want keep the
year 2006. But the records that need the year changed, I can put those in a
seperate table. Then make the changes and append them back to the original
table.
 
J

John Spencer

IF you can identify with criteria which records you need to update then you
don't need to move them out of the table at all.

UPDATE [Your Table]
SET [Action Date] = DateAdd("yyyy",1,[Action Date])
WHERE Year([Action Date]) = 2006 AND
[Some Other field] = "Some Value"

In the query grid,
Field: [Action Date]
Table: [Your table]
Criteria:
Update To: DateAdd("yyyy",1,[Your Table].[Action Date])

Field: Year([Action Date])
Table:
Criteria: = 2006
Update To: <<leave blank>>

Field: [Some Other Field]
Table: [Your Table]
Criteria: = "some Value"
Update To: <<leave blank>>

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top