Results 1 to 10 of 10

Genealogy Age Education

  1. #1
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    9

    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
    1,292
    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 Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,421
    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 (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,319
    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
    9
    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
    9
    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
    9
    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 Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,421
    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 (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  9. #9
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    9
    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,319
    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.

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

Similar Threads

  1. MSAccess Genealogy DB - help!
    By PipSqueek in forum Access
    Replies: 13
    Last Post: 10-11-2018, 04:30 PM
  2. Beginner Designing Genealogy Database
    By Povo in forum Access
    Replies: 33
    Last Post: 11-25-2017, 01:10 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
  •  
Tech Forums: Microsoft Office Forums