Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculating age from a specific date in a form

Discussion in 'Information Technology' started by Cris, Oct 31, 2005.

  1. Cris

    Cris
    Expand Collapse
    Guest

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

    Similar Threads Forum Date
    SciTech Calculating the Rarity of a Fingerprint Breaking News Dec 13, 2010
    Basics Of Sikhi Jagraj Singh Diagnosed With Stage 4 Cancer Sikh Organisations Thursday at 4:12 AM
    1984 Images Revisited - YouTube History of Sikhism Nov 2, 2016
    50 Or So Pages Of DG Are By Guru Gobind Singh Other Scriptures Sep 9, 2016
    Interracial And Interfaith Marriage, Help Needed To Confront/convince My Parents Love & Marriage Aug 16, 2016

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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" <Cris@discussions.microsoft.com> 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.
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    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.
    > 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.
     
  5. Ken Snell [MVP]

    Ken Snell [MVP]
    Expand Collapse
    Guest

    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
    <MS ACCESS MVP>


    "Cris" <Cris@discussions.microsoft.com> 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.
     
  6. Cris

    Cris
    Expand Collapse
    Guest

    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" <Cris@discussions.microsoft.com> 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.

    >
    >
    >
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Cris,

    If by "I don't have my date set up as you do down below" you mean the
    [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?
     
  8. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    I'm not sure what you mean by 'don't have my date set up'.

    --
    Brendan Reynolds

    "Cris" <Cris@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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.

    >>
    >>
    >>
     
  9. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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" <brenreyn@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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.
    >>>
    >>>
    >>>

    >
    >
     
  10. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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" <NOSPAM_djsteele@NOSPAM_canada.com> 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" <brenreyn@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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.
    >>>>
    >>>>
    >>>>

    >>
    >>

    >
    >
     
  11. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Brendan Reynolds wrote:
    > 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
    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
     
  12. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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" <schapel@mvps.org.ns> wrote in message
    news:Os3O73a3FHA.3000@TK2MSFTNGP12.phx.gbl...
    > Brendan Reynolds wrote:
    >> 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 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
     
  13. Cris

    Cris
    Expand Collapse
    Guest

    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" <NOSPAM_djsteele@NOSPAM_canada.com> 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" <brenreyn@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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.
    > >>>>
    > >>>>
    > >>>>
    > >>
    > >>

    > >
    > >

    >
    >
    >
     
  14. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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" <Cris@discussions.microsoft.com> 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
    >> 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" <NOSPAM_djsteele@NOSPAM_canada.com> 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" <brenreyn@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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.
    >> >>>>
    >> >>>>
    >> >>>>
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>
    >>
     
  15. Cris

    Cris
    Expand Collapse
    Guest

    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" <Cris@discussions.microsoft.com> 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
    > >> 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" <NOSPAM_djsteele@NOSPAM_canada.com> 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" <brenreyn@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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.
    > >> >>>>
    > >> >>>>
    > >> >>>>
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
  16. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

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

    --
    Brendan Reynolds


    "Cris" <Cris@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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
    >> >> 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" <NOSPAM_djsteele@NOSPAM_canada.com> 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" <brenreyn@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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" <Cris@discussions.microsoft.com> 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.
    >> >> >>>>
    >> >> >>>>
    >> >> >>>>
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  17. Wes

    Wes
    Expand Collapse
    Guest

    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
     
  18. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    DateSerial(Year(Date()), 1, 1)

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


    "Wes" <Wes@discussions.microsoft.com> 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
     

Share This Page