breaking age into year and month 3

[barny2006]

hi,
is there a way to use modula or something like that to break age into years and month accurately in a single expression in access sql? i'm using
int(test_dt - dob)/365.25 to get the year, but it's rounding up. so somebody 12 years and 9 months old, gets 13 years for yr value. and i'm calculating month:
int(test_dt - dob)/ 30.41
which somewhat is accurate. but the year part is what i'm having problem.

thanks.

 

[SkipVought]

Hi,

INT((test_dt - DOB)/365.25)

Skip,

_[red][/red]

 

[barny2006]

i tried int(test_dt - dob) / 365.25 but it's rounding it up. so when somebody is 12years and 10 months old, it returns 13 instead of 12.

 

[barny2006]

i found a way to do it:

age_mo = ((spec_dt-dob)/30.416) Mod 12, 
age_yrs = ((((spec_dt-dob)/30.416))-(((spec_dt-dob)/30.416) Mod 12))/12;

the month portion is simple: using modulus math which uses 12 and puts the remainder in months
the years portion subtracts months from total months and divides the leftover by 12, which will give exact years without rounding.

 

[SkipVought]

Barny,

You did NOT look close enough at the PARENTHESES

My formula is NOT

int(test_dt - dob) / 365.25

it IS

int((test_dt - dob) / 365.25)

Skip,

_[red][/red]

 

[PHV]

And what about this ?
DateDiff('yyyy', DOB, spec_dt) + (Format(DOB, 'mmdd') > Format(spec_dt, 'mmdd'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barny2006]

sorry, my bad.
you're right. that would work.

 

[barny2006]

thanks phv.
i will try this one also.

 

[barny2006]

phv,
actually yours worked even more accurately than the modulus one. even by one day. it calculates year correctly. then i took the result and multiplied by 12 to subtract from months to get the remaining months.
thanks.

 

[PHV]

To get the months ACCURATELY:
DateDiff('m', DOB, spec_dt) + (Day(DOB) > Day(spec_dt))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

how's this for month...

INT((test_dt-DOB)/(365.25/12)) MOD 12 + 1

Skip,

_[red][/red]

 

[barny2006]

skip,
it works out fine.
thanks!.

 

[lespaul]

then give the man a star!

have one on me skip!

les

 

[MichaelRed]

again, this has been covered in some detail in the past. a search with key words like DOB, Month, and other creative thoughts would producs any number of also quite useable resuls. I would HOPE that the efforts of many / most would be to at least TRY to do some research on existing soloutions before asking the learned members here to create the specific wheel ...




MichaelRed

 

[barny2006]

can anyone explain how i would go about searching the site for keywords as it was suggested? i have tried searches in the past with no luck. maybe i'm doing something wrong.
thanks.

 

[MichaelRed]

at the top of the pages there is a red "button: in the vicinity of three variations of Search. I generally use the "link" to advanced search (right clicking to open in a new window). The search form seems (to me) to be quite straight forward / self explanatory - if somewhat simplistic and limited. The search "Term" is a single entity where you can enter various "Terms" the option buttons below provide the limited flexibility to focus the search (to "All Words:, "Any Words" ... and fora selection ("My Fora", "All fora", "FAQs") The only other control is to do the search - which returns the records found matching the criteria.

A few caveats:

regardless of the (actual) search results, a max of 100 hits are returned.

Only the header of the thread(s) are returned.

Clicking on one of the threads takes you to the thread IN THE FORUM which is the same as finding the thread in the Forum, so you HAVE visited the forum ... meaning that when you return to the search thread list all of the "New" flags are reset for the forum.

Search "Terms" automatically check for standard variation of words (plurals, etc) do entering the variation(s) is NOT helpful.

The Thread headers [b[DO NOT[/b] include the "Stars" like the normal forum full list, so you cannot use these to help find the "Best" threads. (This WAS in previous versions, but sofware "Rot" has apparently set in).

Like wise, the member contribution (red check mark) is not supported, so you cannot use search to usefully find a thread where you provided a "Brilliant Soloution" to some question in hte past).

Hope this helps at least a bit. (mayhap even a byte?)




MichaelRed