convert century from 1900 to 2000

[jomama203]

I inherited data on an old computer and succesfully got info off it and into
access 2007 but the century is wrong 1901 instead of 2001 etc.
I have been using the find and replace function to update them but it's
going to take me forever. is there an easier way?

 

[Jerry Whittle]

Is the year in its own field like a number or text field? Or is it part of a
date field that you are talking about? Also how far do the records go back?
Are there any valid records from the 1900's like 1999?

 

[KARL DEWEY]

I have a similar problem some time after 2000 when I changed over from a dB3
database to access. I had to add 36525 days to each date (100 years).

 

[jomama203]

It is in it's own field and data type is short date. dd/mm/yyyy There are
records that are valid back to 1986. I have tried the update query but must
not be doing it right.
thanks for your help!

 

[John W. Vinson]

It is in it's own field and data type is short date. dd/mm/yyyy There are
records that are valid back to 1986. I have tried the update query but must
not be doing it right.

I'd run an Update query with a criterion

< #1/1/1986#

to get only those records early in the century, and update the field to

DateAdd("yyyy", 100, [nameofthedatefield])

 

[jomama203]

Still didn't work for me. Tried it several times.

this is what I put in update to line
DateAdd("yyyy",100,[DateLastPaid])
and criteria
<#1/1/1986#
Jo

It is in it's own field and data type is short date. dd/mm/yyyy There are
records that are valid back to 1986. I have tried the update query but must
not be doing it right.

I'd run an Update query with a criterion

< #1/1/1986#

to get only those records early in the century, and update the field to

DateAdd("yyyy", 100, [nameofthedatefield])

 

[John W. Vinson]

Still didn't work for me. Tried it several times.

this is what I put in update to line
DateAdd("yyyy",100,[DateLastPaid])
and criteria
<#1/1/1986#
Jo

Please post the actual SQL of the query: open the query in design view; select
View... SQL, and copy and paste the SQL text to a message here. It might help
to also doublecheck the dates actually in the field.

You *did* say this was a Date/TIme field, not text that looks like a date,
right? And in what way does it "not work"?

 

[KARL DEWEY]

Still didn't work for me. Tried it several times.
What was the results? Post the SQL of your update query.

Run a simple query to test field --
SELECT TOP 10 [DateLastPaid], Val([DateLastPaid]) AS DateValCk
FROM YourTable;

What are the results?

--
Build a little, test a little.

Still didn't work for me. Tried it several times.

this is what I put in update to line
DateAdd("yyyy",100,[DateLastPaid])
and criteria
<#1/1/1986#
Jo

It is in it's own field and data type is short date. dd/mm/yyyy There are
records that are valid back to 1986. I have tried the update query but must
not be doing it right.

I'd run an Update query with a criterion

< #1/1/1986#

to get only those records early in the century, and update the field to

DateAdd("yyyy", 100, [nameofthedatefield])

 

[jomama203]

UPDATE [TEST UPdate] SET [TEST UPdate].DateLastPaid =
DateAdd("yyyy",100,[DateLastPaid])
WHERE ((([TEST UPdate].DateLastPaid)<#1/1/1986#));

last valid date is 10/21/1995 dates I need to update range from 1/27/1900 to
4/23/1907

Nothing happens at all when I click on red exclamation RUN
thanks for all your help everybody
Jo

Still didn't work for me. Tried it several times.

this is what I put in update to line
DateAdd("yyyy",100,[DateLastPaid])
and criteria
<#1/1/1986#
Jo

Please post the actual SQL of the query: open the query in design view; select
View... SQL, and copy and paste the SQL text to a message here. It might help
to also doublecheck the dates actually in the field.

You *did* say this was a Date/TIme field, not text that looks like a date,
right? And in what way does it "not work"?

 

[John W. Vinson]

UPDATE [TEST UPdate] SET [TEST UPdate].DateLastPaid =
DateAdd("yyyy",100,[DateLastPaid])
WHERE ((([TEST UPdate].DateLastPaid)<#1/1/1986#));

last valid date is 10/21/1995 dates I need to update range from 1/27/1900 to
4/23/1907

Nothing happens at all when I click on red exclamation RUN
thanks for all your help everybody

I wonder if at some point you have turned off Warnings? Press Ctrl-G and type

DoCmd.SetWarnings True

in the Immediate window at the bottom of the screen. Then run the query again.
Assuming that the TEST UPdate table in fact contains records with a Date/Time
field named DateLastPaid, and that these records have dates prior to 1986,
this should give you a popup saying

"You are about to update xxx records"

or words to that effect; and you should then be able to go into the table and
find that the 1900-1907 dates are now 2000-2007.

 

[jomama203]

John,
when i typed "set warnings true" it said warnings were on.
I will use the find and replace function for each date I need to change.
Good effort and thanks everybody for your help.

--
Jo

UPDATE [TEST UPdate] SET [TEST UPdate].DateLastPaid =
DateAdd("yyyy",100,[DateLastPaid])
WHERE ((([TEST UPdate].DateLastPaid)<#1/1/1986#));

last valid date is 10/21/1995 dates I need to update range from 1/27/1900 to
4/23/1907

Nothing happens at all when I click on red exclamation RUN
thanks for all your help everybody

I wonder if at some point you have turned off Warnings? Press Ctrl-G and type

DoCmd.SetWarnings True

in the Immediate window at the bottom of the screen. Then run the query again.
Assuming that the TEST UPdate table in fact contains records with a Date/Time
field named DateLastPaid, and that these records have dates prior to 1986,
this should give you a popup saying

"You are about to update xxx records"

or words to that effect; and you should then be able to go into the table and
find that the 1900-1907 dates are now 2000-2007.

 

[John W. Vinson]

John,
when i typed "set warnings true" it said warnings were on.
I will use the find and replace function for each date I need to change.
Good effort and thanks everybody for your help.

This is absurd. There is SOMETHING else going on. I created a little table
matching these fieldnames, entered 1900's data, ran the query and it fixed
them.

If the data isn't confidential, or if you trust me (honest, I will delete the
database after I test it) could you zip the database and email it to me at
jvinson <at> wysard of info <dot> com (edit out the blanks)? I'd like to find
out where my advice went wrong.

 

[jomama203]

John,
I am emailing you the data. It's probably some little step I am missing.
Thank you again for all the help.

 

[John W. Vinson]

John,
I am emailing you the data. It's probably some little step I am missing.
Thank you again for all the help.

Returned, with a working query.

I wonder if you were just opening the query as a datasheet with the View icon
(which will show you the pre-update data) rather than running it by pressing
the red exclamation mark Run icon.

 

[jomama203]

Thank You! I was using the red exclamation point but nothing happened.

 

[jomama203]

I knew it was probably something simple. It still wouldn't work but then I
saw a notice at bottom of screen the query was blocked because of security.
It works now. Need to pay more attention to my surroundings I guess.

Jo

 

[John W. Vinson]

I knew it was probably something simple. It still wouldn't work but then I
saw a notice at bottom of screen the query was blocked because of security.
It works now. Need to pay more attention to my surroundings I guess.

That's a relief... I was about to suggest reinstalling Office! <g>

I knew A2007 was picky about VBA code security, I didn't realize that it
affected action queries as well. Putting the database into a Trusted Site
should prevent such problems in the future.