locked
Acess database RRS feed

  • Question

  • I am creating a database to track my family history.  I am trying to determine an individuals age at the time of death; however, if they are living, I would like for the field to enter living or remain blank; either will work. 

    My expression currently reads: Age: DateDiff("yyyy",[Birth],([Died]))

    Thursday, November 8, 2018 8:44 PM

Answers

  • I guess the data type of the column [Died] is datetime. You can allow NULL in that field. Only when the person died, you put date and time there. So you can try this:

    DATEDIFF('yyyy', [Birth], CASE WHEN [Died] IS NOT NULL THEN [Died] ELSE GETDATE() END)

    or

    DATEDIFF('yyyy', [Birth], CASE WHEN ISDATE([Died]) = 1 THEN [Died] ELSE GETDATE() END)


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Thursday, November 8, 2018 10:00 PM
  • Your expression will not yield 100% correct results.  Issues such as leap years cause problems.  You should google for some working code.
    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Thursday, November 8, 2018 10:04 PM
  • Hi GailT088,

    According to your description, my understanding is that you want to calculate the individuals age at the time you insert data into the table. If anything is misunderstood, please tell me.

    Here is my test script.

    DECLARE @Birth datetime,
    @Died datetime
    SET @Birth=Birthday
    Set @Died=Diedday
    insert into table(Birth,Died,age)
    SElECT @Birth,@Died,DATEDIFF(YEAR, @Birth, @Died)

    When the person is living, I set Died = NULL. I insert two records, one Died=’2005-12-31’ and other Died= NULL. 


    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Friday, November 9, 2018 7:03 AM
  • Hi,

    >> I would like for the Died field to remain empty or the word living to be entered into the Died field.  Either is perfectly fine with me.
    Have you tried the replies above? If Died is null, the age returns will be null. The Died field will remain empty if you can allow NULL in that field. I guess the data type of Died is datetime or date, so you cannot enter the word ‘living’ which is a string. 


    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Thursday, November 15, 2018 5:57 AM
  • I repeat. The OP specified "Access Database" as the post title, which implies MS Access.

    The working expression posted is also an Access expression, further supporting that assumption.

    I posted VBA to return the age from a birthdate in Access.

    Whether or not the back end is SQL Server or ACE is not relevant to that.

    So, did you bother to try my suggestion, or any of the others?


    With Joy Wend Your Way

    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Thursday, November 15, 2018 12:30 PM

All replies

  • I guess the data type of the column [Died] is datetime. You can allow NULL in that field. Only when the person died, you put date and time there. So you can try this:

    DATEDIFF('yyyy', [Birth], CASE WHEN [Died] IS NOT NULL THEN [Died] ELSE GETDATE() END)

    or

    DATEDIFF('yyyy', [Birth], CASE WHEN ISDATE([Died]) = 1 THEN [Died] ELSE GETDATE() END)


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Thursday, November 8, 2018 10:00 PM
  • Your expression will not yield 100% correct results.  Issues such as leap years cause problems.  You should google for some working code.
    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Thursday, November 8, 2018 10:04 PM
  • Hi GailT088,

    According to your description, my understanding is that you want to calculate the individuals age at the time you insert data into the table. If anything is misunderstood, please tell me.

    Here is my test script.

    DECLARE @Birth datetime,
    @Died datetime
    SET @Birth=Birthday
    Set @Died=Diedday
    insert into table(Birth,Died,age)
    SElECT @Birth,@Died,DATEDIFF(YEAR, @Birth, @Died)

    When the person is living, I set Died = NULL. I insert two records, one Died=’2005-12-31’ and other Died= NULL. 


    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Friday, November 9, 2018 7:03 AM
  • Although you posted this in a SQL Server Database Design forum, I assume from your post title and Access specific expression, you really want to do this in Access. On that basis

    Public Function CurrentAge(ByVal dtBirthdate As Variant, ByVal dtDateToCompare As Variant) As Variant
        Dim intPreBirthdate As Integer
        If Not IsDate(dtDateToCompare) Then dtDateToCompare = Date
        If IsDate(dtBirthdate) Then
            intPreBirthdate = Date < DateSerial(Year(Date), Month(dtBirthdate), Day(dtBirthdate))
            CurrentAge = DateDiff("yyyy", dtBirthdate, dtDateToCompare) + intPreBirthdate
        Else
            CurrentAge = vbNullString
        End If
    
    End Function


    With Joy Wend Your Way

    Saturday, November 10, 2018 10:24 PM
  • Hi,

    Any update on this? If you have resolved your issue, please close the thread by marking the useful reply as answer.
    In addition, if you have another questions, you could open a new thread and feel free to ask.
    Thanks for your contribution.



    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 14, 2018 3:16 AM
  • Thanks for responding, yes.

     I am at the intermediate and it there in need of some basic instructions. 

    Example my grandfather was born June 25, 1864 he died January 1, 1937.  The formula below yield he was 73 at the time of his death

    Age: DateDiff("yyyy",[Birth],([Died]))

    My sister was born July 17, 1955 she is still living.  I would like for the Died field to remain empty or the word living to be entered into the Died field.  Either is perfectly fine with me.

    I very much appreciate each of your assistance.


    Thursday, November 15, 2018 4:32 AM
  • Hi,

    >> I would like for the Died field to remain empty or the word living to be entered into the Died field.  Either is perfectly fine with me.
    Have you tried the replies above? If Died is null, the age returns will be null. The Died field will remain empty if you can allow NULL in that field. I guess the data type of Died is datetime or date, so you cannot enter the word ‘living’ which is a string. 


    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Thursday, November 15, 2018 5:57 AM
  • I repeat. The OP specified "Access Database" as the post title, which implies MS Access.

    The working expression posted is also an Access expression, further supporting that assumption.

    I posted VBA to return the age from a birthdate in Access.

    Whether or not the back end is SQL Server or ACE is not relevant to that.

    So, did you bother to try my suggestion, or any of the others?


    With Joy Wend Your Way

    • Marked as answer by GailT088 Friday, November 16, 2018 2:01 PM
    Thursday, November 15, 2018 12:30 PM
  • GM, I really do appreciate your assistance, I did not try your suggestion as I stated, I a very intermediate and do not understand SQL, I did provide an example of how I calculated the age and was hoping to elicit from the community the back-end of the formula (the leave blank if the died field is empty). 

    I have been asked by several members to close the thread and will do so now. 

    Thanks  

    Friday, November 16, 2018 1:56 PM
  • thanks ... I will try 
    Friday, November 16, 2018 1:58 PM
  • I see.

    In the VBA I offered, the result is returned as a variant. That means you can supply either a number or a string, as appropriate. In the procedure, the Else branch is set up to return null. However, you can change that to virtually anything you want:

        Else
            CurrentAge = vbNullString

    could be

        Else
            CurrentAge = "Still Living"


    With Joy Wend Your Way

    Friday, November 16, 2018 2:41 PM