Calculating age from a specific date in a form

Brendan Reynolds wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6204-calculating-age-from-specific-date-form.html
> Cris, I still think the problem may lie in the record source

Brendan, I think that may be what Cris meant where he said 'don't have
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
my date set up', in other words referring to the last part of your
earlier post where you asked "does the record source of the form include
the DOB field?

--
Steve Schapel, Microsoft Access MVP

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Thanks Steve. I interpreted Cris's "yes it does" to mean "yes the record
source does include the DOB field" but I guess we'll just have to wait for
clarification.

--
Brendan Reynolds


"Steve Schapel" wrote in message
news:Os3O73a3FHA.3000@TK2MSFTNGP12.phx.gbl...
> Brendan Reynolds wrote:
>> Cris, I still think the problem may lie in the record source
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>
> Brendan, I think that may be what Cris meant where he said 'don't have my
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> date set up', in other words referring to the last part of your earlier
> post where you asked "does the record source of the form include the DOB
> field?
>
> --
> Steve Schapel, Microsoft Access MVP

It's bound directly to the table. And it wouldn't work the way I wrote the
expression above. I had to develop another text box to hold my default date
and convert the date difference from both dates into month's and divide by 12:
=Int(DateDiff("m",[DOB],[DetermDate])/12). Now it works perfectly. I can
change my default determination date whenever I need to or lock it in place
so that it always calculates from that specific date. I'm not sure why the
other way wouldn't work in my form. It kept giving me the error.

"Brendan Reynolds" wrote:

> Ah, thanks Doug. I actually copied and pasted the expression, including the
> original date format, Access automatically changed it to match my regional
> settings.
>
> Cris, I still think the problem may lie in the record source - is the form
> bound directly to the table, or is it bound to a query or SQL statement
> based on the table? If a query or SQL statement, are you *sure* that query
> or SQL statement includes the DOB field in the SELECT clause?
>
> --
> Brendan Reynolds
>
> "Douglas J. Steele" wrote in message
> news:uz65rwZ3FHA.2552@TK2MSFTNGP10.phx.gbl...
> >I think he's talking that you used dd/mm/yyyy in your example, while his
> >original example was mm/dd/yyyy.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> >
> > "Brendan Reynolds" wrote in message
> > news:uSIwo4Y3FHA.3036@TK2MSFTNGP15.phx.gbl...
> >> I'm not sure what you mean by 'don't have my date set up'.
> >>
> >> --
> >> Brendan Reynolds
> >>
> >> "Cris" wrote in message
> >> news:F36763ED-ADAC-4465-8BC6-D2FBFBEFD60C@microsoft.com...
> >>> Yes It does. and putting a $ instead of # in my post was a typo on my
> >>> part
> >>> in the expression itself it does have the # . I don't have my date set
> >>> up as
> >>> you do down below though, Could that be the problem?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> >>>
> >>> "Brendan Reynolds" wrote:
> >>>
> >>>> I copied and pasted your expression, changing only the name of the DOB
> >>>> field, to match the name of a field in my test table ...
> >>>>
> >>>> =DateDiff("yyyy",[BirthDate],#30/04/2006#)+(Format([BirthDate],"mmdd")>Format(#30/04/2006#,"mmdd"))
> >>>>
> >>>> .... and could not reproduce the error. So there doesn't seem to be
> >>>> anything
> >>>> wrong in the expression itself. This leads me to wonder if the error
> >>>> might
> >>>> be in the record source of the form - does the record source of the
> >>>> form
> >>>> include the DOB field?
> >>>>
> >>>> --
> >>>> Brendan Reynolds
> >>>>
> >>>> "Cris" wrote in message
> >>>> news:6C80A0E4-6E50-4C2A-892B-4795966BB049@microsoft.com...
> >>>> > I'm trying to calculate DOB based off the specific date 4/30/2006.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> >>>> > I've
> >>>> > read
> >>>> > all the posts and have entered the following expression in the
> >>>> > control
> >>>> > souce
> >>>> > of an unbound text box in my form:
> >>>> > =DateDiff("yyyy",[DOB],$4/30/2006#)+(Format([DOB],"mmdd")>Format(#4/30/2006#,"mmdd")).
> >>>> > [DOB] is a field in my table.
> >>>> > It is not doing anything. When I transfer back to the data entry
> >>>> > view of
> >>>> > my form #Name? appears in the box that should contain/display my
> >>>> > calculation.
> >>>> > What am I doing wrong? I'm getting very frustrated. Any help anyone
> >>>> > could
> >>>> > give would be great. Is there another way to caculated the age.
> >>>>
> >>>>
> >>>>
> >>
> >>
> >
> >
>
>
>

Unfortunately, that expression is not accurate. For example, I was born on
the 22nd of May. That expression would return an incorrect result for my
birth date on the 1st of May to the 21st of May, inclusive.

--
Brendan Reynolds

"Cris" wrote in message
news:8FE4DADC-B19C-43B9-8AC2-9093A13F1666@microsoft.com...
> It's bound directly to the table. And it wouldn't work the way I wrote
> the
> expression above. I had to develop another text box to hold my default
> date
> and convert the date difference from both dates into month's and divide by
> 12:
> =Int(DateDiff("m",[DOB],[DetermDate])/12). Now it works perfectly. I can
> change my default determination date whenever I need to or lock it in
> place
> so that it always calculates from that specific date. I'm not sure why
> the
> other way wouldn't work in my form. It kept giving me the error.
>
> "Brendan Reynolds" wrote:
>
>> Ah, thanks Doug. I actually copied and pasted the expression, including
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>> the
>> original date format, Access automatically changed it to match my
>> regional
>> settings.
>>
>> Cris, I still think the problem may lie in the record source - is the
>> form
>> bound directly to the table, or is it bound to a query or SQL statement
>> based on the table? If a query or SQL statement, are you *sure* that
>> query
>> or SQL statement includes the DOB field in the SELECT clause?
>>
>> --
>> Brendan Reynolds
>>
>> "Douglas J. Steele" wrote in message
>> news:uz65rwZ3FHA.2552@TK2MSFTNGP10.phx.gbl...
>> >I think he's talking that you used dd/mm/yyyy in your example, while his
>> >original example was mm/dd/yyyy.
>> >
>> > --
>> > Doug Steele, Microsoft Access MVP
>> > http://I.Am/DougSteele
>> > (no e-mails, please!)
>> >
>> >
>> >
>> > "Brendan Reynolds" wrote in
>> > message
>> > news:uSIwo4Y3FHA.3036@TK2MSFTNGP15.phx.gbl...
>> >> I'm not sure what you mean by 'don't have my date set up'.
>> >>
>> >> --
>> >> Brendan Reynolds
>> >>
>> >> "Cris" wrote in message
>> >> news:F36763ED-ADAC-4465-8BC6-D2FBFBEFD60C@microsoft.com...
>> >>> Yes It does. and putting a $ instead of # in my post was a typo on
>> >>> my
>> >>> part
>> >>> in the expression itself it does have the # . I don't have my date
>> >>> set
>> >>> up as
>> >>> you do down below though, Could that be the problem?
>> >>>
>> >>> "Brendan Reynolds" wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>> >>>
>> >>>> I copied and pasted your expression, changing only the name of the
>> >>>> DOB
>> >>>> field, to match the name of a field in my test table ...
>> >>>>
>> >>>> =DateDiff("yyyy",[BirthDate],#30/04/2006#)+(Format([BirthDate],"mmdd")>Format(#30/04/2006#,"mmdd"))
>> >>>>
>> >>>> .... and could not reproduce the error. So there doesn't seem to be
>> >>>> anything
>> >>>> wrong in the expression itself. This leads me to wonder if the error
>> >>>> might
>> >>>> be in the record source of the form - does the record source of the
>> >>>> form
>> >>>> include the DOB field?
>> >>>>
>> >>>> --
>> >>>> Brendan Reynolds
>> >>>>
>> >>>> "Cris" wrote in message
>> >>>> news:6C80A0E4-6E50-4C2A-892B-4795966BB049@microsoft.com...
>> >>>> > I'm trying to calculate DOB based off the specific date 4/30/2006.
>> >>>> > I've
>> >>>> > read
>> >>>> > all the posts and have entered the following expression in the
>> >>>> > control
>> >>>> > souce
>> >>>> > of an unbound text box in my form:
>> >>>> > =DateDiff("yyyy",[DOB],$4/30/2006#)+(Format([DOB],"mmdd")>Format(#4/30/2006#,"mmdd")).
>> >>>> > [DOB] is a field in my table.
>> >>>> > It is not doing anything. When I transfer back to the data entry
>> >>>> > view of
>> >>>> > my form #Name? appears in the box that should contain/display my
>> >>>> > calculation.
>> >>>> > What am I doing wrong? I'm getting very frustrated. Any help
>> >>>> > anyone
>> >>>> > could
>> >>>> > give would be great. Is there another way to caculated the age.
>> >>>>
>> >>>>
>> >>>>
>> >>
>> >>
>> >
>> >
>>
>>
>>

But what I need the age result to be is based on the month and year of birth
not so much the day. I need to figure a players league age based off a cut
off of April 30 of the coming year. So if a player enters our program today
and is 7 years old now but born in April of 98 his league age would be 8
because he will reach the age of 8 by the end of April. If his DOB is May of
98 his league age is 7 because he was still 7 in the month of April. The
expression I put in place is working that way. It's not giving me the exact
Years Months Days, but I don't need it to. I just need the Year age the
player would be by the end of the month of April. Is there another way to
calculate this. Remember my knowledge is very limited. This is the first
time I've ever worked with Access. Thanks for your help.

"Brendan Reynolds" wrote:

> Unfortunately, that expression is not accurate. For example, I was born on
> the 22nd of May. That expression would return an incorrect result for my
> birth date on the 1st of May to the 21st of May, inclusive.
>
> --
> Brendan Reynolds
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>
> "Cris" wrote in message
> news:8FE4DADC-B19C-43B9-8AC2-9093A13F1666@microsoft.com...
> > It's bound directly to the table. And it wouldn't work the way I wrote
> > the
> > expression above. I had to develop another text box to hold my default
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> > date
> > and convert the date difference from both dates into month's and divide by
> > 12:
> > =Int(DateDiff("m",[DOB],[DetermDate])/12). Now it works perfectly. I can
> > change my default determination date whenever I need to or lock it in
> > place
> > so that it always calculates from that specific date. I'm not sure why
> > the
> > other way wouldn't work in my form. It kept giving me the error.
> >
> > "Brendan Reynolds" wrote:
> >
> >> Ah, thanks Doug. I actually copied and pasted the expression, including
> >> the
> >> original date format, Access automatically changed it to match my
> >> regional
> >> settings.
> >>
> >> Cris, I still think the problem may lie in the record source - is the
> >> form
> >> bound directly to the table, or is it bound to a query or SQL statement
> >> based on the table? If a query or SQL statement, are you *sure* that
> >> query
> >> or SQL statement includes the DOB field in the SELECT clause?
> >>
> >> --
> >> Brendan Reynolds
> >>
> >> "Douglas J. Steele" wrote in message
> >> news:uz65rwZ3FHA.2552@TK2MSFTNGP10.phx.gbl...
> >> >I think he's talking that you used dd/mm/yyyy in your example, while his
> >> >original example was mm/dd/yyyy.
> >> >
> >> > --
> >> > Doug Steele, Microsoft Access MVP
> >> > http://I.Am/DougSteele
> >> > (no e-mails, please!)
> >> >
> >> >
> >> >
> >> > "Brendan Reynolds" wrote in
> >> > message
> >> > news:uSIwo4Y3FHA.3036@TK2MSFTNGP15.phx.gbl...
> >> >> I'm not sure what you mean by 'don't have my date set up'.
> >> >>
> >> >> --
> >> >> Brendan Reynolds
> >> >>
> >> >> "Cris" wrote in message
> >> >> news:F36763ED-ADAC-4465-8BC6-D2FBFBEFD60C@microsoft.com...
> >> >>> Yes It does. and putting a $ instead of # in my post was a typo on
> >> >>> my
> >> >>> part
> >> >>> in the expression itself it does have the # . I don't have my date
> >> >>> set
> >> >>> up as
> >> >>> you do down below though, Could that be the problem?
> >> >>>
> >> >>> "Brendan Reynolds" wrote:
> >> >>>
> >> >>>> I copied and pasted your expression, changing only the name of the
> >> >>>> DOB
> >> >>>> field, to match the name of a field in my test table ...
> >> >>>>
> >> >>>> =DateDiff("yyyy",[BirthDate],#30/04/2006#)+(Format([BirthDate],"mmdd")>Format(#30/04/2006#,"mmdd"))
> >> >>>>
> >> >>>> .... and could not reproduce the error. So there doesn't seem to be
> >> >>>> anything
> >> >>>> wrong in the expression itself. This leads me to wonder if the error
> >> >>>> might
> >> >>>> be in the record source of the form - does the record source of the
> >> >>>> form
> >> >>>> include the DOB field?
> >> >>>>
> >> >>>> --
> >> >>>> Brendan Reynolds
> >> >>>>
> >> >>>> "Cris" wrote in message
> >> >>>> news:6C80A0E4-6E50-4C2A-892B-4795966BB049@microsoft.com...
> >> >>>> > I'm trying to calculate DOB based off the specific date 4/30/2006.
> >> >>>> > I've
> >> >>>> > read
> >> >>>> > all the posts and have entered the following expression in the
> >> >>>> > control
> >> >>>> > souce
> >> >>>> > of an unbound text box in my form:
> >> >>>> > =DateDiff("yyyy",[DOB],$4/30/2006#)+(Format([DOB],"mmdd")>Format(#4/30/2006#,"mmdd")).
> >> >>>> > [DOB] is a field in my table.
> >> >>>> > It is not doing anything. When I transfer back to the data entry
> >> >>>> > view of
> >> >>>> > my form #Name? appears in the box that should contain/display my
> >> >>>> > calculation.
> >> >>>> > What am I doing wrong? I'm getting very frustrated. Any help
> >> >>>> > anyone
> >> >>>> > could
> >> >>>> > give would be great. Is there another way to caculated the age.
> >> >>>>
> >> >>>>
> >> >>>>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

If it does what you need, Cris, that's fine. Congratulations on solving your
problem.

--
Brendan Reynolds


"Cris" wrote in message
news:A7B39AFF-1B5B-4637-9531-CDFEF929FB38@microsoft.com...
> But what I need the age result to be is based on the month and year of
> birth
> not so much the day. I need to figure a players league age based off a
> cut
> off of April 30 of the coming year. So if a player enters our program
> today
> and is 7 years old now but born in April of 98 his league age would be 8
> because he will reach the age of 8 by the end of April. If his DOB is May
> of
> 98 his league age is 7 because he was still 7 in the month of April. The
> expression I put in place is working that way. It's not giving me the
> exact
> Years Months Days, but I don't need it to. I just need the Year age the
> player would be by the end of the month of April. Is there another way to
> calculate this. Remember my knowledge is very limited. This is the first
> time I've ever worked with Access. Thanks for your help.
>
> "Brendan Reynolds" wrote:
>
>> Unfortunately, that expression is not accurate. For example, I was born
>> on
>> the 22nd of May. That expression would return an incorrect result for my
>> birth date on the 1st of May to the 21st of May, inclusive.
>>
>> --
>> Brendan Reynolds
>>
>> "Cris" wrote in message
>> news:8FE4DADC-B19C-43B9-8AC2-9093A13F1666@microsoft.com...
>> > It's bound directly to the table. And it wouldn't work the way I wrote
>> > the
>> > expression above. I had to develop another text box to hold my default
>> > date
>> > and convert the date difference from both dates into month's and divide
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>> > by
>> > 12:
>> > =Int(DateDiff("m",[DOB],[DetermDate])/12). Now it works perfectly. I
>> > can
>> > change my default determination date whenever I need to or lock it in
>> > place
>> > so that it always calculates from that specific date. I'm not sure why
>> > the
>> > other way wouldn't work in my form. It kept giving me the error.
>> >
>> > "Brendan Reynolds" wrote:
>> >
>> >> Ah, thanks Doug. I actually copied and pasted the expression,
>> >> including
>> >> the
>> >> original date format, Access automatically changed it to match my
>> >> regional
>> >> settings.
>> >>
>> >> Cris, I still think the problem may lie in the record source - is the
>> >> form
>> >> bound directly to the table, or is it bound to a query or SQL
>> >> statement
>> >> based on the table? If a query or SQL statement, are you *sure* that
>> >> query
>> >> or SQL statement includes the DOB field in the SELECT clause?
>> >>
>> >> --
>> >> Brendan Reynolds
>> >>
>> >> "Douglas J. Steele" wrote in
>> >> message
>> >> news:uz65rwZ3FHA.2552@TK2MSFTNGP10.phx.gbl...
>> >> >I think he's talking that you used dd/mm/yyyy in your example, while
>> >> >his
>> >> >original example was mm/dd/yyyy.
>> >> >
>> >> > --
>> >> > Doug Steele, Microsoft Access MVP
>> >> > http://I.Am/DougSteele
>> >> > (no e-mails, please!)
>> >> >
>> >> >
>> >> >
>> >> > "Brendan Reynolds" wrote in
>> >> > message
>> >> > news:uSIwo4Y3FHA.3036@TK2MSFTNGP15.phx.gbl...
>> >> >> I'm not sure what you mean by 'don't have my date set up'.
>> >> >>
>> >> >> --
>> >> >> Brendan Reynolds
>> >> >>
>> >> >> "Cris" wrote in message
>> >> >> news:F36763ED-ADAC-4465-8BC6-D2FBFBEFD60C@microsoft.com...
>> >> >>> Yes It does. and putting a $ instead of # in my post was a typo
>> >> >>> on
>> >> >>> my
>> >> >>> part
>> >> >>> in the expression itself it does have the # . I don't have my
>> >> >>> date
>> >> >>> set
>> >> >>> up as
>> >> >>> you do down below though, Could that be the problem?
>> >> >>>
>> >> >>> "Brendan Reynolds" wrote:
>> >> >>>
>> >> >>>> I copied and pasted your expression, changing only the name of
>> >> >>>> the
>> >> >>>> DOB
>> >> >>>> field, to match the name of a field in my test table ...
>> >> >>>>
>> >> >>>> =DateDiff("yyyy",[BirthDate],#30/04/2006#)+(Format([BirthDate],"mmdd")>Format(#30/04/2006#,"mmdd"))
>> >> >>>>
>> >> >>>> .... and could not reproduce the error. So there doesn't seem to
>> >> >>>> be
>> >> >>>> anything
>> >> >>>> wrong in the expression itself. This leads me to wonder if the
>> >> >>>> error
>> >> >>>> might
>> >> >>>> be in the record source of the form - does the record source of
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>> >> >>>> the
>> >> >>>> form
>> >> >>>> include the DOB field?
>> >> >>>>
>> >> >>>> --
>> >> >>>> Brendan Reynolds
>> >> >>>>
>> >> >>>> "Cris" wrote in message
>> >> >>>> news:6C80A0E4-6E50-4C2A-892B-4795966BB049@microsoft.com...
>> >> >>>> > I'm trying to calculate DOB based off the specific date
>> >> >>>> > 4/30/2006.
>> >> >>>> > I've
>> >> >>>> > read
>> >> >>>> > all the posts and have entered the following expression in the
>> >> >>>> > control
>> >> >>>> > souce
>> >> >>>> > of an unbound text box in my form:
>> >> >>>> > =DateDiff("yyyy",[DOB],$4/30/2006#)+(Format([DOB],"mmdd")>Format(#4/30/2006#,"mmdd")).
>> >> >>>> > [DOB] is a field in my table.
>> >> >>>> > It is not doing anything. When I transfer back to the data
>> >> >>>> > entry
>> >> >>>> > view of
>> >> >>>> > my form #Name? appears in the box that should contain/display
>> >> >>>> > my
>> >> >>>> > calculation.
>> >> >>>> > What am I doing wrong? I'm getting very frustrated. Any help
>> >> >>>> > anyone
>> >> >>>> > could
>> >> >>>> > give would be great. Is there another way to caculated the
>> >> >>>> > age.
>> >> >>>>
>> >> >>>>
>> >> >>>>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

How do I get the first day of the current year using the system date?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
I need to have a query give me "01-01-YYYY" no mater what year it is.
How do I get this?

Thank you,
gene

DateSerial(Year(Date()), 1, 1)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Wes" wrote in message
news:3C7323A1-C984-4ED5-B512-81E1C041D3FE@microsoft.com...
> How do I get the first day of the current year using the system date?
> I need to have a query give me "01-01-YYYY" no mater what year it is.
> How do I get this?
>
> Thank you,
> gene