Calculated Field_compute age components in 3 columns 4

[MwTV]

Have a birth date field in a sql server database and interested in calculating the age within a Microsoft Access passthrough query.

Is it possible to use three calculated fields in Access so that I receive the Year,Month and Days portion of the age calculation in three separate columns?

Other option is to construct a function that give me the number of days in one column if the first option is not doable.

Any insight?

I am aware of the MS Excel formula that results in the age as Years Months and Days into one cell as displayed below, where BirthDate is in cell "D4";

=DATEDIF(D4,TODAY(),"y")&" years "&DATEDIF(D4,TODAY(),"ym")&" months "&DATEDIF(D4,TODAY(),"md")&" days"


Thanks in advance.

 

[dinger2121]

Not sure what you are looking for...you could use calculated columns in your query as follows -

day([birthday])
month([birthday])
year([birthday])

hope this helps....

 

[dhookom]

If you want the final value derived in the pass-through then you need to ask this question in a SQL Server programming forum.

If you have the date field delivered to Access and have the opportunity to use an Access expression or function, take a look at Doug Steele's functional function at

http://www.accessmvp.com/djsteele/Diff2Dates.html.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

thread183-1385558

Public Function basDOB2AgeExt(DOB As Date, Optional AsOf As Date = -1) As String


    'Michael Red    5/23/02
    'Dob is just the date of Birth
    'AsOf is an optional date to check - as in examples 1 & 2
    'Otherwise, the DOB is checked against the Current Date

    '? basDOB2AgeExt(#8/21/42#)
    '59 Years 9 Months and 2 Days.

    '? basDOB2AgeExt(#8/21/1942#, #8/20/2022#)
    '79 Years 11 Months and 30 Days.

    '? basDOB2AgeExt(#8/21/1942#, #8/21/2022#)
    '80 Years 0 Months and 0 Days.

    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim tmpDt As Date           'Date to use in intermediate Calcs
    Dim DtCorr As Boolean       'BirthDay Before or After date in question
    Dim YrsAge As Integer
    Dim MnthsAge As Integer     'Additional Mnths
    Dim DaysAge As Integer      'Additional Days

    If (AsOf = -1) Then         'Check for (Optional Date to Check against)
        AsOf = Date             'If Not Supplied, Assume Today
    End If

    tmpAge = DateDiff("YYYY", DOB, AsOf)        'Just the Years considering Jan 1, Mam
    DtCorr = DateSerial(Year(AsOf), Month(DOB), Day(DOB)) > AsOf

    YrsAge = tmpAge + DtCorr           'Just Years and Correction
    tmpDt = DateAdd("yyyy", YrsAge, DOB)

    MnthsAge = DateDiff("m", tmpDt, AsOf)
    DtCorr = DateAdd("m", MnthsAge, tmpDt) > AsOf
    MnthsAge = MnthsAge + DtCorr

    tmpDt = DateAdd("m", MnthsAge, tmpDt)
    DaysAge = DateDiff("d", tmpDt, AsOf)

    basDOB2AgeExt = YrsAge & " Years " & MnthsAge & " Months and " & DaysAge & " Days."

End Function
[code]



MichaelRed

 

[MwTV]

Thanks for the functions.

At the risk of redundancy;

Initially performed the query in Sql Query Analyzer and saved as a text file. Then, I imported the file into the MS Access Db.

Prior to the first post, I comtemplated the creation of a sql passthrough in MS Access. Note, the sql script that I currently use in SQL Query Analyzer has "date of birth" as one of the fields. So, I had envisioned "running" the query as is in a MS Access passthrough query and then populating three additional fields within an Access table such as "Age_YearPortion", "Age_MonthPortion", and "Age_DaysPortion."

Now, the functions provided really appears to be the way to go!

How do I incorporate?

Specifically, since I already have the approximately 150,000 records in an MS Access table, how do I use the function to populate the additional three columns, "Age_YearPortion", "Age_MonthPortion", and "Age_DaysPortion?"

Further, for the future, how would I include the "Age_YearPortion", "Age_MonthPortion", and "Age_DaysPortion" in the query results?


Just trying to gain an understanding.

 

[dhookom]

You only need to store the date of birth. You calculate the other columns/values with the functions where ever you need them. Don't store values that are so easy to derive.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

to use/incorporate, examint the sample usag at the top of the procedure/module. the return value (as written) returns a single string. To use w/o change, the calling program parses the string for the six "words" and (presumably) selects the 1st (as Years), the 3rd (as months) and the 5th (as Days).

The (?obvious?) alternative would be to edit / revise the procedure to return the data in whatever form/format suits your purpose.

per the estimable Mr. Hookom, the results should generally NOT be saved to persistant media, so the format of the return value(s) should be considered carefully.



MichaelRed

 

[MwTV]

Well,

I understand the advice to not store calculated fields but I am directed to do so.

Will test the function.

 

[lespaul]

Then you'll need to be sure to create another function that when an incorrect date of birth is entered and then corrected that the process is run on the corrected date of birth. So everywhere in your program where the date of birth could possibly be changed needs to have your new function called to updated the parsed fields. This is one of the main reasons that storing calculated fields is discouraged.

If it was all done in a query, once the date field is corrected every query will be correct. If however, you miss updating all three fields whenever the date of birth is corrected you will be reporting erroneous information.

Good luck!





Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[MichaelRed]

at the considerable risk of offending some one, you MIGHT presume to explain the rationale to whom ever 'requires' you to do this. They OBVIOUSLY are not technical, or they would have never made the request / requirements and as such have no *&^&^%&^%&^%& business directing this level of detail. Phrases like *(&^%*&^%*&^%$&^%$&* MICRO manager come readily to mind.




MichaelRed