Results 1 to 13 of 13
  1. #1
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17

    Question Genealogy Age Education

    Dear Sirs, can someone please help! I am putting together a genealogy database. In my query, I have an age calculation which works fine, my issue is that I do not want some of my age numbers to be shown on my Form for the people who have passed away. Only the people living. I have a checkbox for "Living/Died". Can anyone give me the query calculation to allow me to tie up the checkbox and age calculation?

    I am a novice at Access Database, and a bit long in the tooth, but am enjoying the challenge of Access. Thank you.


  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What do you have so far in your age calculation, and what are the field names? And what do you want to display if they are no longer with us?

    If it's just on the form you could use conditional formatting?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  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
    Assuming your checkbox is called chkLiving (and if ticked they are alive) and you have a textbox txtAge then
    Code:
    If Me.chkLiving=True Then 
    Me.txtAge.Visible =True
    Else
    Me.txtAge.Visible=False
    End If
    Or more concisely
    Code:
    Me.txtAge.Visible=Me.chkLiving
    Assuming you have a single from with one record visible at a time, place the code in the Form_Current event

    Hope that helps
    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

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would do this calculation in the query driving the form or subform (assuming you're using bound forms)

    i.e.

    PersonAge: iif([Deceased] = -1, null, <age calculation here>)

  5. #5
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17
    Hello, thank you for your help and reply, this is what I have put into my query now, PersonAge: iif([Deceased] = -1, null, Age: Year(Now())-Year([DOB]) But now I get an error message; The Expression you entered contains invalid syntax!.

    I am a novice at Access so please excuse my ignorance. also, can you explain a bound form, not heard this expression before? I do appreciate your help. I am using Access 2019.

  6. #6
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17
    Thank you for your help, I am not quite ready at the moment for VBA, but I will try, but I will need time on this. I do appreciate your help, thank you.

  7. #7
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17
    Hello, thank you for your help, this is what I am using in my query for the my Age Calculation, which appears to work fine for just the age. Age: Year(Now())-Year([DoB])

    Thank you.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Your age calculation will only be correct after a person's birth day in any year. Before that date it will be one year out

    There are many examples online that do work reliably. For example https://www.everythingaccess.com/tut...-given-the-DOB
    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

  9. #9
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17
    Sorry for my lack of knowledge, but now I understand "bound" have been very busy lately trying to sort this issue out. I am still getting an error message on my query entry.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    this is what I typically use for an age calculation assuming both dates are present:

    iif(format([dob], "MMDD") > format(Date(), "MMDD") 0, -1) + datediff("yyyy", [dob], date())

    You may have to play with the 0/-1 but what this is doing is seeing if the current date (month and day, not year) is larger than the birth date (month and day, not year), if it's not it subtracts 1 from the difference of the two dates in years. This accounts for people who have not yet had a birthdate in the current year.

  11. #11
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17
    Dear All, sorry for the delay in getting back to you, my query looks like this now, it shows me the age when the person died but now I am missing the age for the living people. All the above suggestions did not work, and I am not sure why. PersonAge: Year([Death])-Year([DOB]) ​ Slowly I am getting there and learning all the time, at my age its difficult some times.

    Click image for larger version. 

Name:	FT Screenshot.jpg 
Views:	18 
Size:	91.4 KB 
ID:	39987


  12. #12
    rapsr59 is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2014
    Location
    El Paso, TX
    Posts
    7
    The Following Visual Basic Code Should Work For Your Genealogy Form
    Good Look, Richard

    Private Sub LivingStatus_AfterUpdate()

    'Hide The PersonsAge If The Person Is Deceased
    If Me.LivingStatus = True Then
    Me.PersonsAge.Visible = True
    Else
    Me.PersonsAge.Visible = False
    End If

    End Sub

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As previously explained, calculating Age by subtracting Year(Death)-Year(DOB) or Year(Now)-Year(DOB) will often be 1 year out.
    There are formulas that will work reliably such as those in the link I provided.

    As for getting no result for anyone living, as they haven't died, Year(Death) will be null so there's nothing to calculate!
    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. Beginner Designing Genealogy Database
    By Povo in forum Access
    Replies: 34
    Last Post: 10-21-2019, 09:48 AM
  2. MSAccess Genealogy DB - help!
    By PipSqueek in forum Access
    Replies: 13
    Last Post: 10-11-2018, 04:30 PM
  3. Education database
    By QAsh in forum Database Design
    Replies: 3
    Last Post: 06-03-2017, 03:29 PM
  4. Best place to start Access self-education
    By mholst in forum Access
    Replies: 4
    Last Post: 07-19-2013, 04:02 AM
  5. Tracking education hours for employees
    By sephiroth2906 in forum Access
    Replies: 3
    Last Post: 11-15-2011, 03:22 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