Results 1 to 14 of 14
  1. #1
    rwcozens is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Location
    South Africa
    Posts
    7

    Calculate age using a persons identity number


    Morning. I am using 2010 and have a bound field called Policyholder ID number in a table called Policy Information. I would like to calculate how old a person is by using the identity number which is 13 digits (South African ID) and the format is as follows - 7009215069084. Using the first 6 digits this person was born on 21 Sept 1970 and is 42 years old ( Current year of 2012 minus year of birth of 1970 = 42)

    Any ideas as to how I can include a calculated field so once I type in the identity number the age of the person reflects in a field called Current Age.

    Thanks

    Richard

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    just check out, if below gives some guidelines :

    Code:
    SELECT 
    	myTable.PolicyID, 
    	Left([PolicyID],2) AS TheYear, 
    	Mid([PolicyID],3,2) AS TheMonth, 
    	Mid([PolicyID],5,2) AS TheDate, 
    	DateSerial([TheYear],[TheMonth],[TheDate]) AS TheDateOfBirth, 
    	DateDiff("yyyy",[TheDateOfBirth],Date()) AS TheAge
    FROM 
    	myTable;
    Wondering whether, Year should be stored as 2 digits or 4 digits in the ID Number.

    Thanks

  3. #3
    rwcozens is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Location
    South Africa
    Posts
    7
    Thanks for the reply. I will try the code you gave me. The year is first 2 numbers in this case 70.

    Richard

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by rwcozens View Post
    The year is first 2 numbers in this case 70.
    1) Do not know how things are at your end. Just check as an example, what kind of results you get if you put the Year as "29" for one PolicyID & "30" for another PolicyID.
    2) Again, the DateDiff I have used is an approximate method for calculating age. There are other ways to calculate age, which are far more accurate, though a bit more complicated.

    Edit :
    Check out :
    Code:
    Age: DateDiff("yyyy", [Birthdate], Now())+ Int( Format(Now(), "mmdd") < Format( [Birthdate], "mmdd") )
    http://www.jegsworks.com/lessons/dat...lues-dates.htm

    Thanks

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If, when checking age, the month and day of the birthday have not yet been reached in the current year, the age calculation will be 1 year too large. So, as recyan indicated, you need to use a more detailed age calculation.

  6. #6
    rwcozens is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Location
    South Africa
    Posts
    7
    Thanks for the rersponses so far. I am still however having problems with the code so I have attached the simplified version of the database I am using.

    Please note on the form that once the Policyholder ID number field is completed that I would like the age of the person to relfect in the ANB field. The month and the day need not be accurate so basically I would like the current year to subtract the year relfected in the POlicyholder ID number field where this year is the first 2 digits of the Policyholder ID number.

    The attached is Access 2010 as it would not allow me to save to earlier version as I have a lot of other features included which older versions do not cater for.

    Thaks

    Richard
    Attached Files Attached Files

  7. #7
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Assuming you have a input text field named PolicyID on your form Form1 & another text field named TheAge.
    In the TheAge Field, in Design mode, insert the below & see what happens :

    Code:
    =DateDiff("yyyy",DateSerial(Left([Forms]![Form1]![PolicyID],2),Mid([Forms]![Form1]![PolicyID],3,2),Mid([Forms]![Form1]![PolicyID],5,2)),Now())+Int(Format(Now(),"mmdd")<Format(DateSerial(Left([Forms]![Form1]![PolicyID],2),Mid([Forms]![Form1]![PolicyID],3,2),Mid([Forms]![Form1]![PolicyID],5,2)),"mmdd"))
    Thanks
    Last edited by recyan; 04-26-2012 at 03:10 AM. Reason: inserted name of Form

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't have Acc2010 so will not be looking at the accdb. However, if you have a field/control that is the Current Year - year of Birth, do NOT call it Age. You will confuse yourself and any who have to maintain your code.

  9. #9
    rwcozens is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Location
    South Africa
    Posts
    7
    Thanks recyan for your input. Reading through your code makes sense however where in Theage properties do I put this code. Will I include it as the control source (doubt it as i want the result to be written to the table) or do I include in an event under code builder. Please can you assist me with this. Richard

  10. #10
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by rwcozens View Post
    Will I include it as the control source
    Yes. You might have to use an IIf() with it, as probably, with an empty PolicyID when the Form is opened, TheAge box might show an error, till you input some policy number.

    Quote Originally Posted by rwcozens View Post
    (doubt it as i want the result to be written to the table)
    Why ?

    Thanks

  11. #11
    rwcozens is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Location
    South Africa
    Posts
    7
    Originally Posted by rwcozens
    Will I include it as the control source



    Yes. You might have to use an IIf() with it, as probably, with an empty PolicyID when the Form is opened, TheAge box might show an error, till you input some policy number.

    I will try using the iif function and post my results. I need to use the reslut of the theage field in other reports so surely then the result must be written to the table or how else can I use it in other reports. Please can you tell me how as obviously I am missing something here.

    Secondly the syntax for the iif() function in the formaula you provided does not work. Could you please complete the syntax for me in other words the entire code to use in the control source of theage text box

    Thanks

  12. #12
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by rwcozens View Post
    I will try using the iif function and post my results.
    Secondly the syntax for the iif() function in the formaula you provided does not work. Could you please complete the syntax for me in other words the entire code to use in the control source of theage text box
    Just check if below gives some guidelines :
    Code:
    =IIf(IsNull([Forms]![Form1]![PolicyID]),0,DateDiff("yyyy",DateSerial(Left([Forms]![Form1]![PolicyID],2),Mid([Forms]![Form1]![PolicyID],3,2),Mid([Forms]![Form1]![PolicyID],5,2)),Now())+Int(Format(Now(),"mmdd")<Format(DateSerial(Left([Forms]![Form1]![PolicyID],2),Mid([Forms]![Form1]![PolicyID],3,2),Mid([Forms]![Form1]![PolicyID],5,2)),"mmdd")))
    Edit : Later on, You might have to add a validation rule based on your PolicyID format to the PolicyID text box in the Form, to alert the user, if it is wrongly entered.

    Quote Originally Posted by rwcozens View Post
    I need to use the reslut of the theage field in other reports so surely then the result must be written to the table or how else can I use it in other reports.
    1) Age is a Calculated figure & unless unavoidable, Calculated values should not be stored in tables.
    2) You can use Queries as the source for Reports & in turn, you can use the query to calculate the Age & present it in the Report. The Calculation will follow along the same lines as we have done in the Form with appropriate replacement of Form values.

    Thanks

  13. #13
    rwcozens is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Location
    South Africa
    Posts
    7
    Thanks very much. It all works now. Well done

  14. #14
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Glad you got things moving.

    Thanks

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

Similar Threads

  1. Replies: 8
    Last Post: 03-30-2012, 06:05 AM
  2. Search Persons (alphabetically)
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-28-2010, 07:31 PM
  3. Replies: 11
    Last Post: 08-04-2010, 04:26 PM
  4. How to associate persons
    By josephbupe in forum Access
    Replies: 1
    Last Post: 02-20-2010, 02:33 PM
  5. How to calculate number of weeks
    By FeatherDust in forum Queries
    Replies: 0
    Last Post: 09-18-2009, 02:50 PM

Tags for this Thread

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