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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Jaime P.

    calculating age by date of birth

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

    Calculating age problems

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

    Calculating Age from DOB

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

    Calculating an age automatically each year

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

    Calculating age from birthdate field

    Guest, Jul 21, 2005, in forum: Microsoft Access
    Replies:
    1
    Views:
    286
    Allen Browne
    Jul 21, 2005
  7. Guest

    Calculating Age

    Guest, Sep 30, 2005, in forum: Microsoft Access
    Replies:
    6
    Views:
    236
    John W. Vinson/MVP
    Oct 1, 2005
  8. Guest
    Replies:
    3
    Views:
    1,097
    Guest
    Oct 15, 2005
Loading...