Help with a date update query

[Question Boy]

I performed a data import from a DOS program into access. Sadly the dates
are messaed up and can't seem to get my update query to work as I need it to.
How can I update my table 'Servicing' wherever the date field 'ServDt' <
1993 so that the 19xx is updated to 20xx? So I need to replace the fist to
numeric values in the date from 19 to 20.

Thank you so very much!

QB

 

[Keith Wilby]

I performed a data import from a DOS program into access. Sadly the dates
are messaed up and can't seem to get my update query to work as I need it
to.
How can I update my table 'Servicing' wherever the date field 'ServDt' <
1993 so that the 19xx is updated to 20xx? So I need to replace the fist
to
numeric values in the date from 19 to 20.

Thank you so very much!

Try this in an update query (untested):

"20" & Right([ServDt],Len([ServDt]) -2)

Back up your data first!

Keith.
www.keithwilby.co.uk

 

[KARL DEWEY]

BACKUP database! BACKUP database! BACKUP database!

If your 'ServDt' is a date field then use DateAdd("yyyy",100, [ServDt]) in
your update query.

 

[John Spencer]

If you are working with a date field then use the dateAdd function to fix the
year.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Use an update query that looks like the following.

UPDATE Servicing
SET ServDt= DateAdd("yyyy",100,[ServDt])
WHERE ServDt < #1993-12-31#

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

 

[Question Boy]

Worked like a charm! Thank you.

BACKUP database! BACKUP database! BACKUP database!

If your 'ServDt' is a date field then use DateAdd("yyyy",100, [ServDt]) in
your update query.
--
KARL DEWEY
Build a little - Test a little

I performed a data import from a DOS program into access. Sadly the dates
are messaed up and can't seem to get my update query to work as I need it to.
How can I update my table 'Servicing' wherever the date field 'ServDt' <
1993 so that the 19xx is updated to 20xx? So I need to replace the fist to
numeric values in the date from 19 to 20.

Thank you so very much!

QB

 

[Jerry Whittle]

A lot depends on the data type of ServDt. If it's text, Access will try to
'help' you by putting double quotes around the 100 in the grid. Therefore it
would need to be done in SQL view. If ServDt is a number, remove the "
around 1993.

UPDATE Servicing SET Servicing.ServDt = [ServDt]+100
WHERE Servicing.ServDt<"1993";