If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date stored as Number
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 |
#2
|
|||
|
|||
Date stored as Number
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)) -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "JanetF" wrote: 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 |
#3
|
|||
|
|||
Date stored as Number
JanetF wrote:
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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Date stored as Number
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 .. "JanetF" wrote in message ... 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 |
#5
|
|||
|
|||
Date stored as Number
On Thu, 8 Feb 2007 11:56:01 -0800, JanetF
wrote: 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] |
#6
|
|||
|
|||
Date stored as Number
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. "John W. Vinson" wrote: On Thu, 8 Feb 2007 11:56:01 -0800, JanetF wrote: 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] |
#7
|
|||
|
|||
Date stored as Number
On Thu, 8 Feb 2007 13:50:01 -0800, JanetF
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|