Date stored as Number

[Guest]

Hi there,

I have a strange situation where I've adopted a database which I need to
take data out of and import into a new database. Unfortunately one of the
tables has the birth date stored as a number, but then an input mask of
99/99/99. This allowed users to enter the birthdate correctly, but when I
try to change the data type to date/time and format it to yy/mm/dd it
immediately changes the date into a bizarre number that doesn't seem to have
anything to do with the date that was in there. I also can't put that data
into a query or new table without the number converting.

My question is, how do I change it to the correct data type and keep the
date as it was entered?? This is bizarre and should be simple, but I can't
figure it out.

Thanks so much in advance!
Janet

 

[Guest]

Debug.Print CDate(540607) = 2/16/3380
I assure you that I wasn't born in the future. Access stores dates as
numbers with 0 being midnight on Saturday, December 30, 1899! A little over a
half million days is a lot of years.

Back to your problem:

Debug.Print CDate(left(540607,2) & "/" & mid(540607,3,2) & "/" &
right(540607,2))
or
CDate(left([BDateField],2) & "/" & mid([BDateField],,3,2) & "/" &
right([BDateField],2))

A problem with CDate is that it will bomb out if it encounters something
that can not be made into a valid date. Therefore run something like this
first to find the bad dates then fix or ignore them.

IsDate(left([BDateField],2) & "/" & mid([BDateField],,3,2) & "/" &
right([BDateField],2))

 

[Marshall Barton]

I have a strange situation where I've adopted a database which I need to
take data out of and import into a new database. Unfortunately one of the
tables has the birth date stored as a number, but then an input mask of
99/99/99. This allowed users to enter the birthdate correctly, but when I
try to change the data type to date/time and format it to yy/mm/dd it
immediately changes the date into a bizarre number that doesn't seem to have
anything to do with the date that was in there. I also can't put that data
into a query or new table without the number converting.

My question is, how do I change it to the correct data type and keep the
date as it was entered?? This is bizarre and should be simple, but I can't
figure it out.

If those numbers are in mmddyy style:
date =DateSerial(bd Mod 100), bs\10000, (bd \ 100) Mod 100)

If they are ddmmyy, reverse the second and third arguments.

 

[John Spencer]

Is the date stored in a text field? or is it stored in a number field?
If so does it have leading zeroes?
Also, is it stored in mmddyy order or in ddmmyy order or in yymmdd order?

In other words if you look at the table and the field what would you see
stored for July 1 2006
060701
60701
070106
010706

Given that information someone can probably help you to convert the data
into a date type.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Hi there,

I have a strange situation where I've adopted a database which I need to
take data out of and import into a new database. Unfortunately one of the
tables has the birth date stored as a number, but then an input mask of
99/99/99. This allowed users to enter the birthdate correctly, but when I
try to change the data type to date/time and format it to yy/mm/dd it
immediately changes the date into a bizarre number that doesn't seem to have
anything to do with the date that was in there. I also can't put that data
into a query or new table without the number converting.

My question is, how do I change it to the correct data type and keep the
date as it was entered?? This is bizarre and should be simple, but I can't
figure it out.

Thanks so much in advance!
Janet

What is the actual datatype - Long Integer? And what is the format of
the number: yymmdd or something else? How does it handle birthdates in
the 19th century? The 21st century?

Assuming that you have values such as 460516 (May 16, 1946), or 20819
(August 18, 2002), you'll need to define a cutoff date for two-digit
years: the simplest might be that any five-digit "date" is assumed to
be in the 2000's. You could then use an expression:

DateSerial([baddate] \ 10000 + IIF([baddate] < 100000, 2000, 1900),
[baddate] MOD 10000 \ 100, [baddate] \ 100)

If your date format is different (mmddyy maybe??) post back, this is
doable.

Note that a friend of mine passed away last November. She was born in
'94 - as was one of her great-great-granddaughters. Whoever designed
this database really must have had their head in the sand!

John W. Vinson [MVP]

 

[Guest]

They've chosen Number as the Data Type and Field Size of Double. Then
there's an input mask of 99/99/99. It looks like it's yy/mm/dd, but 'm not
even sure now if this is really correct as some of them don't look right.
They have visit dates that are numbers as well without the slashes, and some
of those numbers (IF they are also in yy/mm/dd order) don't match up. In
other words they are earlier than the birth date. I'm trying to find out who
created this now as I need some answers. Thanks for the help.

Hi there,

I have a strange situation where I've adopted a database which I need to
take data out of and import into a new database. Unfortunately one of the
tables has the birth date stored as a number, but then an input mask of
99/99/99. This allowed users to enter the birthdate correctly, but when I
try to change the data type to date/time and format it to yy/mm/dd it
immediately changes the date into a bizarre number that doesn't seem to have
anything to do with the date that was in there. I also can't put that data
into a query or new table without the number converting.

My question is, how do I change it to the correct data type and keep the
date as it was entered?? This is bizarre and should be simple, but I can't
figure it out.

Thanks so much in advance!
Janet

What is the actual datatype - Long Integer? And what is the format of
the number: yymmdd or something else? How does it handle birthdates in
the 19th century? The 21st century?

Assuming that you have values such as 460516 (May 16, 1946), or 20819
(August 18, 2002), you'll need to define a cutoff date for two-digit
years: the simplest might be that any five-digit "date" is assumed to
be in the 2000's. You could then use an expression:

DateSerial([baddate] \ 10000 + IIF([baddate] < 100000, 2000, 1900),
[baddate] MOD 10000 \ 100, [baddate] \ 100)

If your date format is different (mmddyy maybe??) post back, this is
doable.

Note that a friend of mine passed away last November. She was born in
'94 - as was one of her great-great-granddaughters. Whoever designed
this database really must have had their head in the sand!

John W. Vinson [MVP]

 

[John W. Vinson]

They've chosen Number as the Data Type and Field Size of Double. Then
there's an input mask of 99/99/99. It looks like it's yy/mm/dd, but 'm not
even sure now if this is really correct as some of them don't look right.
They have visit dates that are numbers as well without the slashes, and some
of those numbers (IF they are also in yy/mm/dd order) don't match up. In
other words they are earlier than the birth date. I'm trying to find out who
created this now as I need some answers. Thanks for the help.

owwwwww....

Good luck. "Garbage in, garbage out" - you may need to just salvage
whatever values are reasonable birthdates and set the rest to NULL,
perhaps keeping the Double number field around for reference (but
*don't* let anybody use or update it!!!)

The DateSerial expression I suggested should work on Double fields.

John W. Vinson [MVP]