Calculating age from a specific date in a form

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6204-calculating-age-from-specific-date-form.html
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204

 
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!

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> 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.

See thiis link:

http://www.mvps.org/access/datetime/date0001.htm
Date/Time: Calculate Age of a person
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



"Cris" wrote:

> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> What am I doing wrong? I'm getting very frustrated. Any help anyone could
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> give would be great. Is there another way to caculated the age.

Change the $ character to a # character; i.e., change this

=DateDiff("yyyy",[DOB],$4/30/2006#)+(Format([DOB],"mmdd")>Format(#4/30/2006#,"mmdd"))

to this

=DateDiff("yyyy",[DOB],#4/30/2006#)+(Format([DOB],"mmdd")>Format(#4/30/2006#,"mmdd"))

--

Ken Snell



"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")).
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> [DOB] is a field in my table.
> It is not doing anything. When I transfer back to the data entry view of
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> 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.

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 ...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>
> =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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
> > 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.
>
>
>

Cris,

If by "I don't have my date set up as you do down below" you mean the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
[DOB] field is not included in the form's record source, then yes, that
will be the problem. You are asking Access to do a calculation on a
form, based on something called [DOB], but how can Access know what
[DOB] means if it's not there? Even if it could discern that you are
referring to a field in a table not related to the form, how would it
know which record in the table to use? Maybe you could explain some
more details about your form and what you are trying to achieve, and
someone may be able to offer another suggestion.

--
Steve Schapel, Microsoft Access MVP

Cris wrote:
> 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?

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.
>> > 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.
>>
>>
>>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>>> 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.
>>>
>>>
>>>
>
>

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...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6204
>>>> > 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.
>>>>
>>>>
>>>>
>>
>>
>
>