Subtract 2 dates in access 2003

Topic

New Reply

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?

Reply | Quote

Replies

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:
[INDENT]Result = DateOfDeath – DOB[/INDENT]

Where “Result” will be the # of days between the 2 dates. Or you could use the DateDiff function if you wanted some other measure.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote