 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2007Converting integers into dates
I have a large database where the Date of birth has been set to type integer of the form dd/mm/yy. I need to perform some calculations on the dates but when I try to just change the type to date it seems to come up with a totally different date. Can anyone suggest how these can be converted and still keep the correct values? Thanks How are you attempting to convert the integers, and how are you attempting to use the resultant dates? If you're trying to use the dates in SQL statements (which includes the Domain Aggregate functions such as DLookup), regardless of what your Short Date format has been set to through Regional Settings, you must use mm/dd/yyyy format (or else an unambiguous one such as yyyy-mm-dd or dd mmm yyyy) You may want to read Allen Browne's "International Dates in Access" at http://www.allenbrowne.com/ser-36.html, or what I had in my September, 2003 "Access Answers" column in Pinnacle Publication's "Smart Access". (You can download the column, and sample database, for free from http://www.accessmvp.com/DJSteele/SmartAccess.html)  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I have a large database where the Date of birth has been set to type >integer [quoted text clipped - 7 lines] > > Thanks > I have a large database where the Date of birth has been set to type > integer of the form dd/mm/yy. Integer? Not Long Integer? An Integer field in Access/Jet can't hold 6-digit numbers. Unless this database is in some other file format, I'm going to assume you mean Long Integer. > I need to perform some calculations on > the dates but when I try to just change the type to date it seems to > come up with a totally different date. > > Can anyone suggest how these can be converted and still keep the > correct values? Do you mean that today's date, for example, would be stored as 290507? If so, you might try an expression like CDate(Mid(CStr([DOB]), 3, 2) & "/" & Mid(CStr([DOB]), 1, 2) & "/" & Mid(CStr([DOB]), 5, 2)) where DOB is the name of the field. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) >> I have a large database where the Date of birth has been set to type >> integer of the form dd/mm/yy. [quoted text clipped - 18 lines] > > where DOB is the name of the field. Since the OP is wanting to use dd/mm/yy, odds are that's the Short Date format on the machine (as set through Regional Settings). That means that while CDate(Mid(CStr([DOB]), 3, 2) & "/" & Mid(CStr([DOB]), 1, 2) & "/" & Mid(CStr([DOB]), 5, 2)) will work fine for a value of 290507 for DOB, it will result in 06 Jan, 2007, not 01 Jun, 2007, when the value for DOB is 010607. Far safer is to use the DateSerial function. However, that does require a 4 digit year, so some logic will have to be used to determine whether the year should be in the 1900s or the 2000s. If we're dealing with DOB, it's likely legimate to use: DateSerial(IIf(Mid(CStr([DOB]), 5, 2) > CStr(Year(Date())), CLng("19" & Mid(CStr([DOB]), 5, 2)), CLng("20" & Mid(CStr([DOB]), 5, 2))), CLng(Mid(CStr([DOB]), 3, 2)), CLng(Mid(CStr([DOB]), 1, 2)) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >Far safer is to use the DateSerial function. However, that does require a 4 >digit year, so some logic will have to be used to determine whether the year [quoted text clipped - 4 lines] >Mid(CStr([DOB]), 5, 2)), CLng("20" & Mid(CStr([DOB]), 5, 2))), >CLng(Mid(CStr([DOB]), 3, 2)), CLng(Mid(CStr([DOB]), 1, 2)) I was curious so I checked: I knew DateSerial was a clever bit of code, but it's even cleverer than I thought! ?dateserial(7,5,29) 5/29/2007 ?dateserial(29,1,1) 1/1/2029 ?dateserial(30,1,1) 1/1/1930 so it uses the familiar two/four digit convention. Dates of birth *really* need to be four digit. There are lots of centenarians around now, and being born in '05 (or even in '98) is still ambiguous! John W. Vinson [MVP] >>Far safer is to use the DateSerial function. However, that does require a >>4 [quoted text clipped - 20 lines] > > so it uses the familiar two/four digit convention. Actually, it makes sense that it would use whatever's defined on the Date tab under Customer Regional Options for the "When a two-digit year is entered, interpret it as a year between", > Dates of birth *really* need to be four digit. There are lots of > centenarians > around now, and being born in '05 (or even in '98) is still ambiguous! No argument from me on this point! SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Since the OP is wanting to use dd/mm/yy, odds are that's the Short > Date format on the machine (as set through Regional Settings). Good point, Doug. I forgot that CDate would use the regional settings. I was thinking it was like Jet's interpretation of date literals. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.