Extract DoB from 10 digit ID Number (1 Viewer)

ljoekelsoey4

Registered User.
Local time
Today, 02:30
Joined
Mar 16, 2017
Messages
49
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
 

ljoekelsoey4

Registered User.
Local time
Today, 02:30
Joined
Mar 16, 2017
Messages
49
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

Banishment Pending
Local time
Yesterday, 21:30
Joined
May 11, 2011
Messages
11,613
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

Registered User.
Local time
Today, 02:30
Joined
Mar 16, 2017
Messages
49
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.
 
Last edited:

Tieval

Still Clueless
Local time
Today, 02:30
Joined
Jun 26, 2015
Messages
475
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

Banishment Pending
Local time
Yesterday, 21:30
Joined
May 11, 2011
Messages
11,613
What does your DateValue look like?
 

ljoekelsoey4

Registered User.
Local time
Today, 02:30
Joined
Mar 16, 2017
Messages
49
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.
 

ljoekelsoey4

Registered User.
Local time
Today, 02:30
Joined
Mar 16, 2017
Messages
49

Users who are viewing this thread

Top Bottom