Changing from 20XX to 19XX date

[Ann]

Is there a way I can *quickly* change the year from 20XX to 19XX?

When I first entered the data, I used this date format xx/xx/xx and all the
years defaulted to 20xx like a count when the period went to xx00.

Hope this makes sense. My boss is asking for a quick solution!

Thanks,
Ann

 

[Ken Snell [MVP]]

You need to change the data? Just use an Update query (make copy of database
before you do this, just in case):

UPDATE TableName
SET [DateFieldName] = DateAdd("yyyy", -100, [DateFieldName]);

 

[Ann]

Ken, you'll have to go slow.
What's an Update Query?
Ann

You need to change the data? Just use an Update query (make copy of database
before you do this, just in case):

UPDATE TableName
SET [DateFieldName] = DateAdd("yyyy", -100, [DateFieldName]);
--

Ken Snell
<MS ACCESS MVP>

Is there a way I can *quickly* change the year from 20XX to 19XX?

When I first entered the data, I used this date format xx/xx/xx and all
the
years defaulted to 20xx like a count when the period went to xx00.

Hope this makes sense. My boss is asking for a quick solution!

Thanks,
Ann

 

[Ken Snell [MVP]]

It's a query that you can run in order to modify (update) data in a table.

What I posted is a sample SQL statement of one.

To create one using the query QBE design view, create a new query in design
view (from database window), select the table whose data need to be updated)
and add it to the query. On the toolbar, there is a Query Type icon (with
dropdown arrow); click it and select Update Query.

Then pull the date field from the table onto the grid. In the Update To:
box, put this expression (using the actual name of the date field in place
of DateFieldName):

DateAdd("yyyy", -100, [DateFieldName])

Clicking the red Exclamation Point icon on the toolbar will run the query
(once run, the data changes cannot be reversed).

--

Ken Snell
<MS ACCESS MVP>

Ken you'll have to go slow. What's an Update query?

Ann

You need to change the data? Just use an Update query (make copy of
database
before you do this, just in case):

UPDATE TableName
SET [DateFieldName] = DateAdd("yyyy", -100, [DateFieldName]);
--

Ken Snell
<MS ACCESS MVP>

Is there a way I can *quickly* change the year from 20XX to 19XX?

When I first entered the data, I used this date format xx/xx/xx and all
the
years defaulted to 20xx like a count when the period went to xx00.

Hope this makes sense. My boss is asking for a quick solution!

Thanks,
Ann

 

[Ann]

Hi Ken,
Thanks for the step by step. I ran it but got 18XX in place of a portion of
the 19XX. The 20XX changed but so did some of the ones that didn't need
changing. How can I fix this?

Ann

It's a query that you can run in order to modify (update) data in a table.

What I posted is a sample SQL statement of one.

To create one using the query QBE design view, create a new query in design
view (from database window), select the table whose data need to be updated)
and add it to the query. On the toolbar, there is a Query Type icon (with
dropdown arrow); click it and select Update Query.

Then pull the date field from the table onto the grid. In the Update To:
box, put this expression (using the actual name of the date field in place
of DateFieldName):

DateAdd("yyyy", -100, [DateFieldName])

Clicking the red Exclamation Point icon on the toolbar will run the query
(once run, the data changes cannot be reversed).

--

Ken Snell
<MS ACCESS MVP>

Ken you'll have to go slow. What's an Update query?

Ann

You need to change the data? Just use an Update query (make copy of
database
before you do this, just in case):

UPDATE TableName
SET [DateFieldName] = DateAdd("yyyy", -100, [DateFieldName]);
--

Ken Snell
<MS ACCESS MVP>


Is there a way I can *quickly* change the year from 20XX to 19XX?

When I first entered the data, I used this date format xx/xx/xx and all
the
years defaulted to 20xx like a count when the period went to xx00.

Hope this makes sense. My boss is asking for a quick solution!

Thanks,
Ann

 

[Ken Snell [MVP]]

Sorry... your post seemed to say that all the dates were 20xx and you wanted
them to be changed to 19xx.

Make a copy of the backup copy of the file (you did make a backup copy, as
I'd suggeted, right/).

In the update query, you'll need to add another field to the grid. Again,
change the DateFieldName to the correct name. In the "Field:" cell, type
this:

TestYear: Year([DateFieldName])

In the "Criteria:" cell under this, type

1999

Leave the "Update To:" cell for this field blank.

Now, when you run the update query, it will change only the dates that have
a year of 2000 or higher.



--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the step by step. I ran it but got 18XX in place of a portion
of
the 19XX. The 20XX changed but so did some of the ones that didn't need
changing. How can I fix this?

Ann

It's a query that you can run in order to modify (update) data in a
table.

What I posted is a sample SQL statement of one.

To create one using the query QBE design view, create a new query in
design
view (from database window), select the table whose data need to be
updated)
and add it to the query. On the toolbar, there is a Query Type icon (with
dropdown arrow); click it and select Update Query.

Then pull the date field from the table onto the grid. In the Update To:
box, put this expression (using the actual name of the date field in
place
of DateFieldName):

DateAdd("yyyy", -100, [DateFieldName])

Clicking the red Exclamation Point icon on the toolbar will run the query
(once run, the data changes cannot be reversed).

--

Ken Snell
<MS ACCESS MVP>

Ken you'll have to go slow. What's an Update query?

Ann

:

You need to change the data? Just use an Update query (make copy of
database
before you do this, just in case):

UPDATE TableName
SET [DateFieldName] = DateAdd("yyyy", -100, [DateFieldName]);
--

Ken Snell
<MS ACCESS MVP>


Is there a way I can *quickly* change the year from 20XX to 19XX?

When I first entered the data, I used this date format xx/xx/xx and
all
the
years defaulted to 20xx like a count when the period went to xx00.

Hope this makes sense. My boss is asking for a quick solution!

Thanks,
Ann