Dates in future

[Linda RQ]

Hi,

I recently ran an update query which I used an excel 2003 file as a linked
table. I took Date Of Birth off the sheet and it was entered into my DOB
field on a table. The problem is that the birthdates dates before 2000 have
converted from 12/16/2007-12/26/2049. These dates should be 1900. What can
I do to change them and how can I prevent it from happening again? I will
be running this update query weekly. I am using Access 2003.

Thanks,
Linda

 

[Douglas J. Steele]

If you know for a fact that no date should be in the future, so that you
want to subtract 100 years from it, use an update query along the lines of:

UPDATE MyTable
SET DOB = DateAdd("yyyy", -100, [DOB])
WHERE DOB > Date()

 

[Steve]

Give a few examples of the dates you entered and what they were converted
to.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Linda RQ]

Date of Birth from Linked Excel Sheet to Date Of Birth Field in Access Table

12/16/07 converted to 12/16/2007
12/26/49 converted to 12/26/2049

I started out with a Report from our Electronic Health Record and converted
it to Excel using a dataminer program. It didn't occure to me that the
dates would convert to the future. These people are alive so their
birthdates can't be in the future.

Thanks,
Linda

 

[Linda RQ]

There are dates in the table that are correct. The only dates that are
incorrect are the future dates. I wouldn't want to subtract 100 years from
someone with an actual DOB of 4/1/2007. There are about 800 records that
are correct. The first wrong DOB is 12/16/2007.

Oh wait....I seeeeeee, Where DOB > Date() This will take care of just dates
after today?

Thanks, I'll try it tomorrow.

Linda

If you know for a fact that no date should be in the future, so that you
want to subtract 100 years from it, use an update query along the lines
of:

UPDATE MyTable
SET DOB = DateAdd("yyyy", -100, [DOB])
WHERE DOB > Date()

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

I recently ran an update query which I used an excel 2003 file as a
linked table. I took Date Of Birth off the sheet and it was entered into
my DOB field on a table. The problem is that the birthdates dates before
2000 have converted from 12/16/2007-12/26/2049. These dates should be
1900. What can I do to change them and how can I prevent it from
happening again? I will be running this update query weekly. I am
using Access 2003.

Thanks,
Linda

 

[Keith Wilby]

Date of Birth from Linked Excel Sheet to Date Of Birth Field in Access
Table

12/16/07 converted to 12/16/2007
12/26/49 converted to 12/26/2049

I started out with a Report from our Electronic Health Record and
converted it to Excel using a dataminer program. It didn't occure to me
that the dates would convert to the future. These people are alive so
their birthdates can't be in the future.

Thanks,
Linda

Try using an "iif" function to detect dates in the future and subtract 100
years from just those records.

Iif([DOB]>Date(),DateAdd("yyyy", -100, [DOB]),[DOB])

Keith.
www.keithwilby.com

 

[Steve]

Are the dates correct in Excel?

Are the years in Excel two digits or four digits?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Linda RQ]

Here is how my dates are displayed on the original excel sheet and on the
excel sheet that is a linked table which I used in my update query.

Linked XLs Table
In design view of the table looking at the date properties
Date/Time Format m/d/yyyy

DOB
7/29/1955
3/11/2043
10/12/1957
4/2/2007
2/4/1960
10/21/1953
5/13/1976
10/1/1972
11/23/2038



Excel Sheet-Cell format is Date, Type is *1/14/2001. In the dialog box it
says Date formats display date and time serial numbers as date values.
Except for items that have an (*), applied formats do not switch date orders
with the operating system.

DOB
7/29/1955
3/11/2043
10/12/1957
4/2/2007
2/4/1960
10/21/1953
5/13/1976
10/1/1972
11/23/2038


The funny thing is, I copied these numbers below just a little while ago
from somewhere and thought it was the problem but I can't find them
again....I thought it was from the original excel file but the above numbers
are from the original excel file.

DOB
7/29/55
3/11/43
10/12/57
4/2/07
2/4/60
10/21/53
5/13/76
10/1/72
11/23/38

Thanks,
Linda

 

[Linda RQ]

OK...I just looked at the excel sheet on my computer here at home and the
dates are with 2 digit years. This is really strange.

Linda

 

[John Marshall, MVP]

Not really. When you supply a two digit year to the program, it has to make
a guess as to whether the first two digits of the year start with 19 or 20.
It appears from your sample that dates with values 50 to 99 are taken to
mean 1950 to 1999. While dates with a value of 0 to 49 are taken to
represent 2000 to 2049. So you need to do some educated guessing. Is it
possible that any of the dates are for people over one hundred? If so, they
may require individual handling. If not, then any date after 2000 (or in the
futre) can be assumed to represent dates from 1900 to 1949.

Note: The 1950 figure is a guess because you show 2043 and 1953, So it may
be that the split is somewhere else between 1944 and 1952.

John... Visio MVP

 

[Linda RQ]

Thanks, John.

Not really. When you supply a two digit year to the program, it has to
make a guess as to whether the first two digits of the year start with 19
or 20. It appears from your sample that dates with values 50 to 99 are
taken to mean 1950 to 1999. While dates with a value of 0 to 49 are taken
to represent 2000 to 2049. So you need to do some educated guessing. Is it
possible that any of the dates are for people over one hundred? If so,
they may require individual handling. If not, then any date after 2000 (or
in the futre) can be assumed to represent dates from 1900 to 1949.

Note: The 1950 figure is a guess because you show 2043 and 1953, So it may
be that the split is somewhere else between 1944 and 1952.

John... Visio MVP