Extract DoB from 10 digit ID Number

[ljoekelsoey4]

I have a 10 digit ID number (i.e 1412893553) which corresponds to a date of birth (14th Dec 1989) and 4 random numbers. Is there code I can use to work out of the date of birth from this ID number? Cheers

 

[plog]

You can use the Mid function (https://www.techonthenet.com/access/functions/string/mid.php) to get specific characters from a string. You can then use DateValue (https://www.techonthenet.com/access/functions/date/datevalue.php) to convert a string to a date

 

[ljoekelsoey4]

Mid function works brilliantly, so I now have 14, 12, 89 respectively in 3 fields in a query, but the datevalue function is a little trickier to get working... The examples on the link you provided only show named months, years (i.e december, 2002, august, 1999) In which case would i need to convert to numbers I have now? If so how would I do that?

 

[plog]

No, I see an example that has this:

DateValue ("6/30/2004")

You need to take your numbers and build a string that looks like that. Use the & to concatenate strings together:

"6" & "/" & "30" & "/" & "2004"

 

[ljoekelsoey4]

No, I see an example that has this:

DateValue ("6/30/2004")

You need to take your numbers and build a string that looks like that. Use the & to concatenate strings together:

"6" & "/" & "30" & "/" & "2004"

Ah yes, excuse. In my ID number, 1412893553, I'm extracting 14 (day), 12 (Month) and 89 (Year 1989) The issue with 89 is that it could be 1889, etc, so access is having trouble, or rather, I'm having trouble making it explicit to Access. Incidentally, how would it handle someone born in 2001? How could one make the distinction between 1901 and 2001 obvious to Access?

EDIT: I've got a way around it, by simply adding 1990 to the result. There are only a few who are born after 2000 so I can manually change them. I'm still not able to get this datevalue function to work however. I'm getting errors in the query saying the expression is not valid.

 

[Tieval]

You cannot.

If it is stored as 1203015555 for 12th March 2001 it will be the same for 1901.

Why not do this the other way around, store the date of birth and then extract a random identifier when required.

 

[plog]

What does your DateValue look like?

 

[ljoekelsoey4]

What does your DateValue look like?

Dob: DateValue([Day] & "/" & [Month] & "/" & [Year])

I built a second query on top of the first to use the fields day, month, and (year + 1900) and it has worked finally. Indeed, even ID numbers starting as 010101 (1st Jan 2001) are defaulting to 2001, and not 1901, so it has worked perfectly.

Thanks a million for your help.

 

[plog]

It looks correct, other than your choice of reserved words (https://support.microsoft.com/en-us...n-access-2002-and-in-later-versions-of-access) for field names.

Can you upload a sample database?

 

[ljoekelsoey4]

It looks correct, other than your choice of reserved words (https://support.microsoft.com/en-us...n-access-2002-and-in-later-versions-of-access) for field names.

Can you upload a sample database?

Perhaps as it was only a query and not a table? From there I essentially copy pasted the DoBs (as date, not text) into the table without any issues. Subsequent functions to calculate age have also worked out. Thanks again for your help.