convert century from 1900 to 2000

  • Thread starter Thread starter jomama203
  • Start date Start date
J

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?
 
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?
 
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).
 
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!
 
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.
Click to expand...


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])
 
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


John W. Vinson said:
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.
Click to expand...


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])
Click to expand...
 
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
Click to expand...

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"?
 
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.


jomama203 said:
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


John W. Vinson said:
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.
Click to expand...


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])
Click to expand...
Click to expand...
 
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
John W. Vinson said:
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
Click to expand...

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"?
Click to expand...
 
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
Click to expand...

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,
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


John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
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.
Click to expand...

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.
 
John,
I am emailing you the data. It's probably some little step I am missing.
Thank you again for all the help.
 
John,
I am emailing you the data. It's probably some little step I am missing.
Thank you again for all the help.
Click to expand...

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.
 
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
 
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.
Click to expand...

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.
 
Back
Top