 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / April 2005How do I calculate age from social security number?
How do I calculate age using a 13 digit social security number where the the first six digits is DOB (yy/MM/dd). I tried Select Left 6 digits and calling that DOB and then do DateDiff but this DOB field is not date format. Can anyone help. I am using Access 2003. Ummmm Social Security numbers do not include a birthdate. (Unless they have "social security numbers" outside the US and I am not aware of it. My social Security number is 452-XX-XXXX. I was born in December of 1968. If that is the case, I'm sure there is a way to turn a six-digit number into a date. I have seen it posted out here before. It seems like it put the digits into the "date serial" somehow. Once you got that, you'd use the following to calcualte age. The proper formula to calculate Age is... DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date( ),"mmdd"))  SignatureRick B > How do I calculate age using a 13 digit social security number where the the > first six digits is DOB (yy/MM/dd). I tried Select Left 6 digits and calling > that DOB and then do DateDiff but this DOB field is not date format. Can > anyone help. I am using Access 2003. Just saw that you said 13-digit. Obviously not a US social security (they are 9 digits). Disregard the first part of my post. SignatureRick B > How do I calculate age using a 13 digit social security number where the the > first six digits is DOB (yy/MM/dd). I tried Select Left 6 digits and calling > that DOB and then do DateDiff but this DOB field is not date format. Can > anyone help. I am using Access 2003. You could use the DateSerial function ... ? dateserial(cint(left$("050101",2)),cint(mid$("050101",3,2)),cint(mid$("050101",5,2))) 01/01/2005 Note, though, that the format is not Y2K compatible. There is no way to tell whether someone with a number begining '050101' is three months old (1 Jan 2005) or 100 years old (1 Jan 1905). SignatureBrendan Reynolds (MVP) > How do I calculate age using a 13 digit social security number where the > the > first six digits is DOB (yy/MM/dd). I tried Select Left 6 digits and > calling > that DOB and then do DateDiff but this DOB field is not date format. Can > anyone help. I am using Access 2003. Perhaps you could enclose the string into # signs to signify that it is a date? Cheers, Pavel > How do I calculate age using a 13 digit social security number where the the > first six digits is DOB (yy/MM/dd). I tried Select Left 6 digits and calling > that DOB and then do DateDiff but this DOB field is not date format. Can > anyone help. I am using Access 2003. >> How do I calculate age using a 13 digit social security number where >> the the first six digits is DOB (yy/MM/dd). I tried Select Left 6 >> digits and calling that DOB and then do DateDiff but this DOB field >> is not date format. > Perhaps you could enclose the string into # signs to signify that it > is a date? No: I she really means the first six digits then it's not going to be a legitimate date format in any language ("031225") with or without the hash marks. Chopping out the digits and passing them to DateSerial is the only safe method. All the best Tim F hi, Here is how to achieve it: Step 1: Separate the part of the number that are the date: Use the function Left(string, length). It returns a Variant (String) containing a specified number of characters from the left side of a string. Example MyStr = Left(SSN, 6) Step 2: Converting the 6 digits to date Repeat the LEFT function and store the values in variables say SSN_DAY, SSN_MONTH, SSN_YEAR Step 3: The DOBDepending upon which date format you are using on your PC (relative to the global settings in Windows) adjust whether you need the day first or the month first. Get DOB as SSN_DOB=SSN_DAY & "-" & SSN_MONTH & "-" & SSN_YEAR Now apply IsDate Function to SSN_DOB to check that this indeed is a date. Convert it to date as SSN_DOB= CDate(SSN_DOB) Use the date diff function with the SSN_DOB and it should return the age Age= DateDiff("y", Now, SNN_DOB) --------------- Haris Rashid http://www.manage-systems.com ---------------------------------------- > How do I calculate age using a 13 digit social security number where the the > first six digits is DOB (yy/MM/dd). I tried Select Left 6 digits and calling > that DOB and then do DateDiff but this DOB field is not date format. Can > anyone help. I am using Access 2003. |
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.