Welcome to SPN

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

Sign Up Now!

Calculating Birthdates

Discussion in 'Information Technology' started by SarahLyn, Jul 28, 2006.

  1. SarahLyn

    SarahLyn
    Expand Collapse
    Guest

    I am having problems. I am trying to get the birthdates to calculate and show
    up in a form. I have been use this

    Age:
    Abs(DateDiff("yyyy",DOB,Date())+(Format(Date(),"mmdd")<Format(DOB,"mmdd")))

    In my query and I get an error of #Name? in my form where the birthday is to
    calculate If some one could help me out.

    Please advise.
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Calculating the Rarity of a Fingerprint Breaking News Dec 13, 2010

  3. Cilla

    Cilla
    Expand Collapse
    Guest

    SarahLyn wrote:
    > I am having problems. I am trying to get the birthdates to calculate and show
    > up in a form. I have been use this
    >
    > Age:
    > Abs(DateDiff("yyyy",DOB,Date())+(Format(Date(),"mmdd")<Format(DOB,"mmdd")))
    >
    > In my query and I get an error of #Name? in my form where the birthday is to
    > calculate If some one could help me out.
    >
    > Please advise.


    The code is good except you do not have brackets around the DOB field.
    Try an unbound field in the form with the same calc below as the
    control source for the unbound field..

    =Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))

    should work fine

    If you are pulling it from a query make sure you have no format on the
    field in the form.
     
  4. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    It is difficult to see what is the purpose of the Abs() function in this
    expression. I don't think it's right. Try this...
    Age:
    DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd"))

    --
    Steve Schapel, Microsoft Access MVP

    Cilla wrote:
    > =Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
     
  5. Berek

    Berek
    Expand Collapse
    Guest

    I trying to determine the age of a person and I was using this command in the
    default value:

    DateDiff("yyyy",[birth_date],Date())

    and when i try to save the table it says

    "The database engine does not recognize either the field 'birth_date' in a
    validation expression, or the default value in the table 'general'

    I believe it is the latter situation, but not sure how to fix it

    Thanks
     
  6. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Calculating Birthdates -- calculated control for Age

    Hi Berek ,

    Since Age is a calculation (and will change), it is something that
    should be shown on a form or report rather than stored. You can't use
    equations like that in the DefaultValue property of a table, but you can
    set a DefaultValue like this for a form control ... but, in this case,
    you wouldn't do that because the equation should be preceeded by an
    equal sign (=) and used as the ControlSource of a calculated control

    You should not put data directly into tables anyway.

    Also, the equation you are using won't work properly if the person was
    born in December and the current month is July because Access will just
    take the difference of the years.

    This was posted by Ricky Hicks:

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    To get the current age .. use:

    DateDiff("yyyy", [DOBField], Date()) +
    (Date() < DateSerial(Year(Date()), Month([DOBField]), Day([DOBField])))

    "DOBField" should be the name of the field that contains the Date of
    Birth value ...
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


    Warm Regards,
    Crystal
    *
    :) have an awesome day :)
    *
    MVP Access



    Berek wrote:
    > I trying to determine the age of a person and I was using this command in the
    > default value:
    >
    > DateDiff("yyyy",[birth_date],Date())
    >
    > and when i try to save the table it says
    >
    > "The database engine does not recognize either the field 'birth_date' in a
    > validation expression, or the default value in the table 'general'
    >
    > I believe it is the latter situation, but not sure how to fix it
    >
    > Thanks
     
  7. Berek

    Berek
    Expand Collapse
    Guest

    Re: Calculating Birthdates -- calculated control for Age

    Thanks a lot, that was very useful and makes much more sense. There is no
    need to store that info.... duh

    Thanks again

    Berek



    "strive4peace" wrote:

    > Hi Berek ,
    >
    > Since Age is a calculation (and will change), it is something that
    > should be shown on a form or report rather than stored. You can't use
    > equations like that in the DefaultValue property of a table, but you can
    > set a DefaultValue like this for a form control ... but, in this case,
    > you wouldn't do that because the equation should be preceeded by an
    > equal sign (=) and used as the ControlSource of a calculated control
    >
    > You should not put data directly into tables anyway.
    >
    > Also, the equation you are using won't work properly if the person was
    > born in December and the current month is July because Access will just
    > take the difference of the years.
    >
    > This was posted by Ricky Hicks:
    >
    > '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    > To get the current age .. use:
    >
    > DateDiff("yyyy", [DOBField], Date()) +
    > (Date() < DateSerial(Year(Date()), Month([DOBField]), Day([DOBField])))
    >
    > "DOBField" should be the name of the field that contains the Date of
    > Birth value ...
    > '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >
    >
    > Warm Regards,
    > Crystal
    > *
    > :) have an awesome day :)
    > *
    > MVP Access
    >
    >
    >
    > Berek wrote:
    > > I trying to determine the age of a person and I was using this command in the
    > > default value:
    > >
    > > DateDiff("yyyy",[birth_date],Date())
    > >
    > > and when i try to save the table it says
    > >
    > > "The database engine does not recognize either the field 'birth_date' in a
    > > validation expression, or the default value in the table 'general'
    > >
    > > I believe it is the latter situation, but not sure how to fix it
    > >
    > > Thanks

    >
     

Share This Page