Is it possible to automatically generate birth dates from birth number?

[pauly]

Hi there! This is a question I have thought about for several years. I have searched on internet but haven’t found anything related. I don’t know if it is possible, but if it can be done, it will be a great time saver.

What I want to achieve is, in a form of employee, after the BirthNumber is entered and the focus is losing, then the BirthDate and Gender data will be automatically generated.

For example, when I input the BirthNumber 0510311001, the BirthDate 31.10.2005 will be automatically generated.

From the above BirthNumber, the first two digits (05) are the last two numbers of the year (if the number beginning with the 7th number is a four digit number then the year is after 1954. If this number is three digit then the year is before 1954). The third and the fourth number (10) form the month (if it is smaller than 50 the gender is male, if it is bigger than 50 then the gender is female). The fifth and the sixth number (31) are the day.

Czech Republic and Slovakia uses a system called Birth Number (Czech/Slovak: rodné číslo (RČ)). The system was introduced in the former Czechoslovakia.

The form is YYXXDD/SSSC, where XX=MM (month of birth) for male (numbers 01–12) and XX=MM+50 for female (numbers 51–62), SSS is a serial number separating persons born on the same date and C is a check digit, but for people born before 1 January 1954 the form is without the check digit - YYXXDD/SSS. This enables the system to work until the year 2054. The whole number must be divisible by 11.

Have you seen something similar?
Any help will be highly appreciated.

BirthNumber-To-BirthDate.odb (14.5 KB)

[RobertG]

Have a look at the attached database
BirthNumber-To-BirthDate.odb (14.8 KB)

You don’t need a separate “Gender” and a separate “BirthDate”. If the information is part of the “BirthNumber” you could extract all from there. The query will fill the fields in your form.

[pauly]

Thank you Robert so much. That is something like an epiphany. Absolutely makes sense. No need to have a separate Gender and BirthDate column.

When I clicked the Query1 in the attached odd file, there are some dark columns, like Gender, Year and Month. Day, BirthD are displayed correctly. Can it be fixed? I think it is only a small display problem. Thank you!!!

[RobertG]

I didn’t get the dark columns here. Don’t know why you get it.

Which system and LO-version do you use? I’m using OpenSUSE 15.3 Linux with LO 7.3.2.2.

What will happen if you use this query as data source for the form (“BirthD” instead of “BirthDate”, “Gen” instead of “Gender”)? Shouldn’t show “Gen” with black background.

[pauly]

Thanks for reminding me. I had an old version LO 7.1.5. I immediately upgraded to LO 7.3.2.2., and it shows perfectly correct now.
Your solution opened my eyes and let me realise that if we can master tables and queries, we can make quite impressive databases already. Thanks again!!! Much appreciated.