Results 1 to 3 of 3
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Date of birth from ID No

    In South Africa our personal Id numbers looks like 7802015063088. 13 Numbers. If the 7th number is a (0,1,2,3 or 4 you are female) (5,6,7,8,9 your male) How would an expression look in a new text field which returns Male or Female. First two is the year born in so 78. Next two is the month 02 so February, next two is the day so the first. I would like to derive the date of birth with expression in another text field after entering the ID no. People i.e. from Zimbabwe does not have their date of birth in their ID no, but we very seldom do business with them. So I will try to get my development to result that when I choose nationality button as Zimbabwe it will force me to enter the Date of birth, but most of them the expression should do it.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a lookup table for gender:
    tGender
    0,F
    1,F
    4,F
    5,M
    6,M
    etc...

    make a base query for your data... bring in the ID field and the gender field,
    Gender: mid([id],7,1)
    BirthYr: left([id],2)

    then in another query, join Gender field to the tGender table to get male/Female
    calculate birth:
    Age:Year(date)-BirthYr

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I would use two simple functions to do this:

    Code:
    Function GetGender(IDNumber) As String
    
    If Mid([IDNumber],7,1)>4 Then
         GetGender="M"
    Else
        GetGender="F"
    End If
    
    End Function

    Code:
    Function GetDOB(IDNumber) As Date
    
    'assumes DOB format is dd/mm/yy but should be displayed using your default date format
    GetDOB = Mid([IDNumber], 5, 2) & "/" & Mid([IDNumber], 3, 2) & "/" & Left([IDNumber], 2)
    
    End Function
    Assuming the data is stored in a table tblClients with fields IDNumber, Gender, DOB, Nationality then use an update query as follows

    Code:
    UPDATE tblClients SET tblClients.Gender = GetGender([IDNumber]), tblClients.DOB = GetDOB([IDNumber])
    WHERE (((tblClients.Nationality)='South African'));
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. get age from date to birth
    By earlcools in forum Queries
    Replies: 2
    Last Post: 01-27-2015, 07:03 PM
  2. How to change date of birth into text
    By Ayiramala in forum Access
    Replies: 13
    Last Post: 12-28-2014, 09:21 PM
  3. Calculate age from Date of Birth
    By djcmalvern in forum Programming
    Replies: 8
    Last Post: 05-03-2013, 06:18 AM
  4. Date of birth converter
    By markyboy in forum Forms
    Replies: 1
    Last Post: 05-23-2011, 07:20 AM
  5. Calculate age from birth date
    By mick in forum Forms
    Replies: 2
    Last Post: 07-03-2010, 04:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums