Update a portion of a date formatted field with an Update query

[GoNowhere]

Hi There,

I'm attempting to update a short date formatted field with a consistent year without changing the Month or Day. Below is what I have so far, when trying to update it however I get a Type Conversion Error and I'm at a loss... Any help would be much appreciated:

UPDATE [New Hires]
SET [New Hires].DOB = Right$([DOB],4) & "1901"
WHERE [New Hires].DOB Not Like "*1901";

Thanks
GN

[nico5038]

Hi There,

I'm attempting to update a short date formatted field with a consistent year without changing the Month or Day. Below is what I have so far, when trying to update it however I get a Type Conversion Error and I'm at a loss... Any help would be much appreciated:

UPDATE [New Hires]
SET [New Hires].DOB = Right$([DOB],4) & "1901"
WHERE [New Hires].DOB Not Like "*1901";

Thanks
GN

Hi GN,

When your field is a real date field, the short date format is "built in" and what you see is not what you get. Access will have stored a relative daynumber and will show a "full" date when you change the format of the field.
When you entered the date Access will use a so-called "breakyear" to add the century and by default the range "00" to "39" is used for the 20th century and "40" to "99" for the 19th century.

Nic;o)

[GoNowhere]

Hi GN,

When your field is a real date field, the short date format is "built in" and what you see is not what you get. Access will have stored a relative daynumber and will show a "full" date when you change the format of the field.
When you entered the date Access will use a so-called "breakyear" to add the century and by default the range "00" to "39" is used for the 20th century and "40" to "99" for the 19th century.

Nic;o)

Hi Nico,

Thanks for the reply. Could you possibly dumb that down one more level, or point me in a direction to accomplish what I'm trying to do?

Thanks again
GN

[nico5038]

Hi Nico,

Thanks for the reply. Could you possibly dumb that down one more level, or point me in a direction to accomplish what I'm trying to do?

Thanks again
GN

Hi GN,

First you need to tell me the datatype of the DOB field in the table :-)

Nic;o)

[GoNowhere]

Hi GN,

First you need to tell me the datatype of the DOB field in the table :-)

Nic;o)

Hi Nico,

The datatype for the field is Date/Time formated to Short Date.

Thanks again,
GN

[GoNowhere]

Hi GN,

First you need to tell me the datatype of the DOB field in the table :-)

Nic;o)

So, I'm a moron. I was going in the wrong direction. The follow works for the most part, still having some difficulty however.

UPDATE [New Hires] SET [New Hires].DOB = left$([New Hires].DOB,6) & "1901"
WHERE [New Hires].DOB Not Like "*1901";

I've reformatted the field to 'MM/DD/YYYY' so the date is consistently 10 characters. It replaces most of the non-1901 dates. But I'm still getting a data type conversion error for some. Not sure why, will investigate further and come back if I can't figure it out.

Thanks again for your time.
Cheers,
GN

[nico5038]

To manipulate the 1900's into 2000's you can use the Dateserial() function, syntax:
Dateserial("Year","Month","Day")
Sample:

UPDATE tblX
set DOB = (Year(DOB)+1000,Month(DOB0,Day(DOB))
Where Year(DOB) < 1939
 

Nic;o)