Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtract 2 dates in access 2003

    Hi I have a form that I'm working in--how does access subtract the DOB from the date of death? would it have to be in the query or in the form?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by buckshot View Post
    Hi I have a form that I'm working in--how does access subtract the DOB from the date of death? would it have to be in the query or in the form?
    What are you trying to get out of this subtraction. You could just do this:
    Result = DateOfDeath - DOB

    Where "Result" will be the # of days between the 2 dates. Or you could use the DateDiff function if you wanted some other measure.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm not sure how to write that
    Result = DateOfDeath - DOB
    would it be in the query or on the form that I'm using?
    How do you even write that? Result[DateOfDeath-[DOB]) this did not work for me I get a error

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Calculated Fields

    This subtraction is just an example of a calculated field.

    You can perform calculations either in a query or directly in a form/report.

    In a query the syntax is : Result: [DateofDeath]-[DOB]

    In a form you set the control source of a text box directly = [DateofDeath]-[DOB]

    You could name the text box result if you want, but that is not essential.
    Regards
    John



  5. #5
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    I use this code in a standard module:

    Code:
    Public Function myGetAge(ByVal bDate As Date) As String
    ' Written by datAdrenaline - modified by Cybercow
    Dim strTemp As String, dtTemp As Date
    Dim yrs As Long, mos As Long, dys As Long
    Dim dblTotalTime As Double
    Dim X As Integer
        
        'Ensure the start is LESS than the stop
        If bDate > Date Then
            MsgBox "No date greater than today is allowed.", vbOKOnly + vbInformation, "Unacceptable Date"
            Exit Function
        End If
        
        'Get the years between the two dates
        yrs = DateDiff("yyyy", bDate, Date)
        yrs = yrs - Abs(DateAdd("yyyy", yrs, bDate) > Date)
        
        'Get the months between the two dates that exceed the years
        mos = DateDiff("m", bDate, Date)
        mos = mos - Abs(DateAdd("m", mos, bDate) > Date) - (yrs * 12)
        
        'Get the number of days between the two dates that exceed the years + months ...
        dys = DateDiff("n", DateAdd("m", mos + yrs * 12, bDate), Date) \ 1440
        
        'Build string for the "left" half of our time difference
        For X = 1 To 3
            strTemp = strTemp & " " & Choose(X, yrs, mos, dys) & " " & Choose(X, "yrs", "mos", "dys")
        Next X
        
        'Return the string
        myGetAge = mid(strTemp, 2)
        
    End Function
    You can call it from a query.



    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert


    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Boyd's function does a similar job the one Buckshot asked about but not quite the same. His function myGetAge tells you how old someone is today. It accepts one parameter, a starting date, and uses the current date as the finish date. To use it to find the age at death you would need to modify the function to accept a second parameter, and use that instead of the Date function for the second date.

    In his first response Mark asked what type of answer you are looking for.
    Simple subtraction gives you just a number of days.
    A function like Boyd's would give a piece of text like: "34 Yrs 5 mths 12 days"
    Another possible answer may just be the age in years.

    Do you need to do anything with the answer other than display it on the screen? If you wanted to perform any further calculations, for example, you might need the answer to be a number.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •