Future date age 1

[corner40]

Hi All
I need to determine the age of someone based on a future date not based on todays date. The field name for the future date is StartDat, the field name for the persons birthday is Birthdate and I want to assign it to field LessonAge.
I already have the age one working:
=Int((Date()-[Birthdate])/365)
but can't get it to work for [StartDat]
I was trying:
=Int(([Forms]![Main]![Kid Zone].[Form]![StartDat]-[Birthdate])/365)
I'm not sure if it's because [StartDat] is on a different subform that i ended up making a mistake or what.
you guys are all super good at this kind of thing so it should be no problem for you.
thanks in advance

 

[MichaelRed]

beside not getting the references correct, the calculation is not correct. The length of a year is only approximatly 365 days, and the correction does influence the age of 'things'.

For A 'correct' function search these fora for "basDOB2Age", you should find a number of references. Review them to see what you might be able to understand and use.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[WildHare]

KidsAge = DateDiff("yyyy", Birthdate, Startdat)

Always works for me.

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MichaelRed]

WildHare

KidsAge = DateDiff("yyyy", Birthdate, Startdat)

Always works for me.

is WRONG 50% of the time.

an explanation -and reference to a (more) correct soloution- noted above.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CosmoKramer]

corner40,

Create a new module and add these two functions to it:

Function PersonAge(Birthdate As Date) As String
PersonAge = DateDiff("yyyy", Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate)))
If PersonAge <= 0 Then PersonAge = DateDiff(&quot;m&quot;, Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate))) & &quot; months&quot;

End Function


Function FutureAge(Birthdate As Date, FutureDate As Date) As String
FutureAge = DateDiff(&quot;yyyy&quot;, Birthdate, FutureDate) + _
(FutureDate < DateSerial(Year(FutureDate), Month(Birthdate), Day(Birthdate)))
If FutureAge <= 0 Then FutureAge = DateDiff(&quot;m&quot;, Birthdate, FutureDate) + _
(FutureDate < DateSerial(Year(FutureDate), Month(Birthdate), Day(Birthdate))) & &quot; months&quot;

End Function

In your query use the PersonAge function to calculate the age based on today's date, and the FutureAge function to calculate it from a future date. Something like this:

CurrentPersonAge: PersonAge([YourBirthDateField])

FuturePersonAge: FutureAge([YourBirthDateField],[YourFutureDateField])

I have tested this thoroughly for a healthcare app I wrote, and it works correctly for those tricky days around a birthday......

Let me know if this helps.......

 

[MichaelRed]

again, search for "basDob2Age" in htese fora, which encapsulates the two functions shown above.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[corner40]

I actually tried the DateDiff example a few times, and tested it for around birthdays, and it seems to work fine. Thanks you cosmokramer and MichaelRed for your help aswell...I'm going to try your solution now and see if it is more stable/reliable.
thanks again all for your help. Greatly appreciated.

 

[WildHare]

[red]Mike[/red], you're right of course - blame it on late-night ennui or something... if the kid's birthday is in the future part of the year, it will make him/her older than he/she really is (which most kids probably wouldn't complain about, anyway..) but is indeed incorrect. I stand (well, sit) corrected.

JMH
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[toffa]

hi all i have implemented code and works great but i HAVE THE THOUGHT with my db that i wanted to see the age as say 2 years 8 months. how would i go about changing the code for that.

Function PersonAge(Birthdate As Date) As String
PersonAge = DateDiff("yyyy", Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate)))
If PersonAge <= 0 Then PersonAge = DateDiff("m", Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate))) & " months"

End Function

is the code i have and works great thanks to cosmokramer

Toffa