DATE IN ACCESS TABLE

[Mary]

Date should read 1929 but shows as 2029 in Access Table how do I change this

 

[Douglas J. Steele]

Sounds as though you entered it using only a 2 digit year. When you use 2
digit years, Windows decides what the actual year should be using the
parameters set through Regional Settings in the Control Panel.

If you've only got one or two such dates, you're probably best off just
going in and changing them manually. For more, use an Update query along the
lines of

UPDATE MyTable
SET MyDateField = DateAdd("yyyy", -100, MyDateField)
WHERE MyDateField > #2028-12-31#

 

[John Spencer]

Type in the full year (all four digits)?

Depending on your settings Access decides the century of a two-digit year
based on whether or not the two-digit year is greater than 29. Any year from
0 to 29 is 21st Century (20) and any year from 30 to 99 is 20th century (19).

If you have a lot of dates that are in the wrong century, you can use an
update query to correct the problem as long as you can reliably identify the
dates that need to be corrected.

For instance to change all the dates that are after 31 December 2008 you would
use something like:

UPDATE YourTable
SET TheDateField = DateAdd("YYYY",-100,[YourTable].[TheDateField])
WHERE TheDateField > #2008.12.31#

Or if this need to be done over time, you could use

UPDATE YourTable
SET TheDateField = DateAdd("YYYY",-100,[YourTable].[TheDateField])
WHERE TheDateField >DateSerial(Year(Date()),12,31)

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

 

[rhodesmk]

You could also check the input mask. If it ends with 00, then it will
convert on it's own. If it ends with 0000, you force the user to input all
four digits.

Now for my question

I'm building a membership directory database. I want birth dates and
anniversary dates, but, being the fickle folks some are, I want those who are
proud of their age to be able to enter month, day, and year, and those who
are a bit more private to be able to enter month and day only, leaving the
year blank. Yes, this could be a free form text field, but I'd rather it be
a time/date field so I can run reports that will let me know who gets
birthday/anniversay notices in our church newsletter.

Thanks! (since this is posted within someone else's question, I'll cross
post in my own subject area...frowned on, yes, but I'm afraid it will be
missed here.)

Type in the full year (all four digits)?

Depending on your settings Access decides the century of a two-digit year
based on whether or not the two-digit year is greater than 29. Any year from
0 to 29 is 21st Century (20) and any year from 30 to 99 is 20th century (19).

If you have a lot of dates that are in the wrong century, you can use an
update query to correct the problem as long as you can reliably identify the
dates that need to be corrected.

For instance to change all the dates that are after 31 December 2008 you would
use something like:

UPDATE YourTable
SET TheDateField = DateAdd("YYYY",-100,[YourTable].[TheDateField])
WHERE TheDateField > #2008.12.31#

Or if this need to be done over time, you could use

UPDATE YourTable
SET TheDateField = DateAdd("YYYY",-100,[YourTable].[TheDateField])
WHERE TheDateField >DateSerial(Year(Date()),12,31)

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

Date should read 1929 but shows as 2029 in Access Table how do I change this

 

[John Spencer]

A date time field REQUIRES that you have a month, a day, and a year.

So you can
-- decide on a default year (say 1850) to indicate dates where the year
is not entered
-- Use a text field to store the date information
-- Use three fields to store the month, the day, and the year (null = no
year given)
-- Use a date field to store the information and another field (boolean)
to indicate that the year should be ignored or treated as inaccurate

Or other solution of your choice.

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

You could also check the input mask. If it ends with 00, then it will
convert on it's own. If it ends with 0000, you force the user to input all
four digits.

Now for my question

I'm building a membership directory database. I want birth dates and
anniversary dates, but, being the fickle folks some are, I want those who are
proud of their age to be able to enter month, day, and year, and those who
are a bit more private to be able to enter month and day only, leaving the
year blank. Yes, this could be a free form text field, but I'd rather it be
a time/date field so I can run reports that will let me know who gets
birthday/anniversay notices in our church newsletter.

Thanks! (since this is posted within someone else's question, I'll cross
post in my own subject area...frowned on, yes, but I'm afraid it will be
missed here.)

Type in the full year (all four digits)?

Depending on your settings Access decides the century of a two-digit year
based on whether or not the two-digit year is greater than 29. Any year from
0 to 29 is 21st Century (20) and any year from 30 to 99 is 20th century (19).

If you have a lot of dates that are in the wrong century, you can use an
update query to correct the problem as long as you can reliably identify the
dates that need to be corrected.

For instance to change all the dates that are after 31 December 2008 you would
use something like:

UPDATE YourTable
SET TheDateField = DateAdd("YYYY",-100,[YourTable].[TheDateField])
WHERE TheDateField > #2008.12.31#

Or if this need to be done over time, you could use

UPDATE YourTable
SET TheDateField = DateAdd("YYYY",-100,[YourTable].[TheDateField])
WHERE TheDateField >DateSerial(Year(Date()),12,31)

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

Date should read 1929 but shows as 2029 in Access Table how do I change this