Expression for calculating age

Discussion in 'Microsoft Access' started by Aaron, Jul 31, 2003.

  1. Aaron

    Aaron Guest

    I'm making a simple conact db in which I want a field to
    automatically calculate the age of a contact using the
    current date and their birthdate. I was running into
    trouble b/c of format issues. Is there a simple way to go
    about this??
     
    Aaron, Jul 31, 2003
    #1
    1. Advertisements

  2. What about Year(Now) - Year([birthdate])?


    "Aaron" <> wrote in message
    news:0cf701c35779$677ed310$...
    > I'm making a simple conact db in which I want a field to
    > automatically calculate the age of a contact using the
    > current date and their birthdate. I was running into
    > trouble b/c of format issues. Is there a simple way to go
    > about this??
     
    Glen SIdelnikov, Jul 31, 2003
    #2
    1. Advertisements

  3. Hi Aaron this worked for me
    I have a field Date of Birth and I created a textbox control called txtAge
    and on its On Enter event put this code
    Private Sub txtAge_Enter()
    Dim dateBirthday As Date
    dateBirthday = Date_of_Birth.Value
    Dim varAge As Variant
    varAge = -DateDiff("yyyy", Date, dateBirthday)
    Dim varDiff As Variant
    varDiff = DateDiff("d", DateAdd("yyyy", varAge, dateBirthday), Date)
    If varDiff < 0 Then
    varAge = varAge - 1
    End If
    txtAge.Value = varAge
    If txtAge.Value < 18 Then
    MsgBox "This person is under 18!", vbOKOnly, "Warning"
    End If
    End Sub

    Hope that helped
    Tony Williams

    "Glen SIdelnikov" <> wrote in message
    news:...
    > What about Year(Now) - Year([birthdate])?
    >
    >
    > "Aaron" <> wrote in message
    > news:0cf701c35779$677ed310$...
    > > I'm making a simple conact db in which I want a field to
    > > automatically calculate the age of a contact using the
    > > current date and their birthdate. I was running into
    > > trouble b/c of format issues. Is there a simple way to go
    > > about this??

    >
    >
     
    Tony Williams, Jul 31, 2003
    #3
  4. Aaron

    Aaron Guest

    That did it...thanks.
    >-----Original Message-----
    >What about Year(Now) - Year([birthdate])?
    >
    >
    >"Aaron" <> wrote in message
    >news:0cf701c35779$677ed310$...
    >> I'm making a simple conact db in which I want a field to
    >> automatically calculate the age of a contact using the
    >> current date and their birthdate. I was running into
    >> trouble b/c of format issues. Is there a simple way to

    go
    >> about this??

    >
    >
    >.
    >
     
    Aaron, Jul 31, 2003
    #4
  5. Aaron

    Allen Guest

    I use this expression in a query: Age =(Now()-[Date of
    Birth])/365. Then in properties set format to standard.
    I usually give it 2 decimal places.

    >-----Original Message-----
    >I'm making a simple conact db in which I want a field to
    >automatically calculate the age of a contact using the
    >current date and their birthdate. I was running into
    >trouble b/c of format issues. Is there a simple way to

    go
    >about this??
    >.
    >
     
    Allen, Jul 31, 2003
    #5
  6. Aaron

    Aaron Guest

    You are right. there is a big hole in the previous
    suggestions. I looked at the website and copied the
    function, but there's an error in the db now. I'm not an
    expert on Access or db language commands - somewhat
    familiar and it intuitively makes sense - but still
    limited. Do I need to create different fields than simply
    a short date field (i.e. one for day, one for month) or
    introduce the new field names in the existing structure?

    Aaron
    >-----Original Message-----
    >If you interested in a function that is accurate (and a

    reason why every one
    >I see in this thread is not) see
    > http://users.bigpond.net.au/abrowne1/func-08.html
    >
    >--
    >Joan Wild
    >Microsoft Access MVP
    >
    >"Aaron" <> wrote in message
    >news:0ebd01c35780$3c494110$...
    >> That did it...thanks.
    >> >-----Original Message-----
    >> >What about Year(Now) - Year([birthdate])?
    >> >
    >> >
    >> >"Aaron" <> wrote in message
    >> >news:0cf701c35779$677ed310$...
    >> >> I'm making a simple conact db in which I want a

    field to
    >> >> automatically calculate the age of a contact using

    the
    >> >> current date and their birthdate. I was running into
    >> >> trouble b/c of format issues. Is there a simple way

    to
    >> go
    >> >> about this??
    >> >
    >> >
    >> >.
    >> >

    >
    >
    >.
    >
     
    Aaron, Aug 1, 2003
    #6
  7. Aaron

    Joan Wild Guest

    I assume you have a field in your table with the person's date of birth.
    This would be a date/time field type.

    You would not actually store their age in the table. Instead you can use
    the function in a query or as a control source of a textbox on a form or
    report.

    Paste the function in a module in your database. Ensure that you don't give
    the module the same name as the function.

    Create a query based on your table. In an empty column of the grid, you
    would put

    Age([Your DOB field])

    When you run the query, that column will show the person's age as of today.


    --
    Joan Wild
    Microsoft Access MVP

    "Aaron" <> wrote in message
    news:0b6301c3583a$77080e70$...
    > You are right. there is a big hole in the previous
    > suggestions. I looked at the website and copied the
    > function, but there's an error in the db now. I'm not an
    > expert on Access or db language commands - somewhat
    > familiar and it intuitively makes sense - but still
    > limited. Do I need to create different fields than simply
    > a short date field (i.e. one for day, one for month) or
    > introduce the new field names in the existing structure?
    >
    > Aaron
     
    Joan Wild, Aug 1, 2003
    #7
  8. Aaron

    Aaron Guest

    ok..I gotcha. Originally, I just copied the function into
    the control source field of the textbox (age). it didn't
    seem to work because I got the #Name? error. That's when
    I thought it was needing to know what the controls were
    referenced in the function. Am I on the right track?
    Also, I will try the other route of creating the module,
    then a query and have the control source ot the textbox
    (age) point to the query. Would that work as well?

    Aaron
    >-----Original Message-----
    >I assume you have a field in your table with the person's

    date of birth.
    >This would be a date/time field type.
    >
    >You would not actually store their age in the table.

    Instead you can use
    >the function in a query or as a control source of a

    textbox on a form or
    >report.
    >
    >Paste the function in a module in your database. Ensure

    that you don't give
    >the module the same name as the function.
    >
    >Create a query based on your table. In an empty column

    of the grid, you
    >would put
    >
    >Age([Your DOB field])
    >
    >When you run the query, that column will show the

    person's age as of today.
    >
    >
    >--
    >Joan Wild
    >Microsoft Access MVP
    >
    >"Aaron" <> wrote in message
    >news:0b6301c3583a$77080e70$...
    >> You are right. there is a big hole in the previous
    >> suggestions. I looked at the website and copied the
    >> function, but there's an error in the db now. I'm not

    an
    >> expert on Access or db language commands - somewhat
    >> familiar and it intuitively makes sense - but still
    >> limited. Do I need to create different fields than

    simply
    >> a short date field (i.e. one for day, one for month) or
    >> introduce the new field names in the existing structure?
    >>
    >> Aaron

    >
    >
    >.
    >
     
    Aaron, Aug 1, 2003
    #8
  9. Aaron

    Joan Wild Guest

    "Aaron" <> wrote in message
    news:6ca701c35866$d745cc10$...
    > ok..I gotcha. Originally, I just copied the function into
    > the control source field of the textbox (age). it didn't
    > seem to work because I got the #Name? error. That's when
    > I thought it was needing to know what the controls were
    > referenced in the function. Am I on the right track?
    > Also, I will try the other route of creating the module,
    > then a query and have the control source ot the textbox
    > (age) point to the query. Would that work as well?
    >
    > Aaron


    You have to create the module, no matter what route you take.

    You then can use the function in a number of ways.

    1. As a calculated column in a query, as described before (and yes then a
    textbox on a form or a report could be bound to this column)

    2. If it's not in the query (but your DOB field is), then on a form or
    report, you can add a textbox, and set it's control source to
    =Age([DOB Field])

    --
    Joan Wild
    Microsoft Access MVP
     
    Joan Wild, Aug 1, 2003
    #9
  10. Aaron

    Aaron Guest

    Success! thanks for the help. I opted to have the text
    box point to the query which contains the Birthdate
    field. Now, if I use the same query to build a report,
    and add columns to the query, it shouldn't mess up the age
    operation should it?

    >-----Original Message-----
    >
    >"Aaron" <> wrote in message
    >news:6ca701c35866$d745cc10$...
    >> ok..I gotcha. Originally, I just copied the function

    into
    >> the control source field of the textbox (age). it

    didn't
    >> seem to work because I got the #Name? error. That's

    when
    >> I thought it was needing to know what the controls were
    >> referenced in the function. Am I on the right track?
    >> Also, I will try the other route of creating the module,
    >> then a query and have the control source ot the textbox
    >> (age) point to the query. Would that work as well?
    >>
    >> Aaron

    >
    >You have to create the module, no matter what route you

    take.
    >
    >You then can use the function in a number of ways.
    >
    >1. As a calculated column in a query, as described before

    (and yes then a
    >textbox on a form or a report could be bound to this

    column)
    >
    >2. If it's not in the query (but your DOB field is), then

    on a form or
    >report, you can add a textbox, and set it's control

    source to
    >=Age([DOB Field])
    >
    >--
    >Joan Wild
    >Microsoft Access MVP
    >
    >
    >.
    >
     
    Aaron, Aug 8, 2003
    #10
  11. Aaron

    Joan Wild Guest

    "Aaron" <> wrote in message
    news:00f001c35dc1$6650aba0$...
    > Success! thanks for the help. I opted to have the text
    > box point to the query which contains the Birthdate
    > field. Now, if I use the same query to build a report,
    > and add columns to the query, it shouldn't mess up the age
    > operation should it?


    No, it shouldn't.

    --
    Joan Wild
    Microsoft Access MVP
     
    Joan Wild, Aug 8, 2003
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jaime P.

    calculating age by date of birth

    Jaime P., Aug 18, 2003, in forum: Microsoft Access
    Replies:
    2
    Views:
    280
    Jaime P.
    Aug 18, 2003
  2. Rusty

    Calculating age problems

    Rusty, May 26, 2004, in forum: Microsoft Access
    Replies:
    1
    Views:
    215
    Douglas J. Steele
    May 26, 2004
  3. Guest
    Replies:
    2
    Views:
    258
    Arvin Meyer
    Oct 28, 2004
  4. Ant

    Calculating Age from DOB

    Ant, Feb 24, 2005, in forum: Microsoft Access
    Replies:
    2
    Views:
    216
  5. Guest

    Calculating an age automatically each year

    Guest, Jul 20, 2005, in forum: Microsoft Access
    Replies:
    11
    Views:
    285
    James A. Fortune
    Jul 22, 2005
Loading...

Share This Page