Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    6

    Unanswered: How to use the Nz function to do age calculations + retrieve from a different field

    Hey guys,
    I've created an Access 2007 database for a vaccination clinic in a slum where I work. In a table named "Table Children" I have a "Date of Birth field" and and an "Age at the time of first joining the program" right next to each other. General practice is to enter the date of the birth of the child but many people don't know their child's date of birth and give an age.

    Basically, I want to calculate the age of the child when he first came to our clinic so I've created a query and I'm doing
    Age: DateDiff("m",[Table Children]![Date of Birth],[Table Children]![Date of entry])

    Date of entry is the date the child came for his first shot.

    The problem is that in some cases I don't have a date of birth just the age the parent has given.

    So I need a function or something that basically calculates age when a date of birth is given and when it isn't given copies age from the "Age at the time of first joining the program". In a different query or the same I'd also like to calculate date of birth where it isn't given.

    I'm thinking I need to use the Nz or IIf function but I'm verrrrryyy new to access so have no idea how this is done.


    Thanks for all the help guys!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by nkk View Post
    Hey guys,
    I've created an Access 2007 database for a vaccination clinic in a slum where I work. In a table named "Table Children" I have a "Date of Birth field" and and an "Age at the time of first joining the program" right next to each other. General practice is to enter the date of the birth of the child but many people don't know their child's date of birth and give an age.

    Basically, I want to calculate the age of the child when he first came to our clinic so I've created a query and I'm doing
    Age: DateDiff("m",[Table Children]![Date of Birth],[Table Children]![Date of entry])

    Date of entry is the date the child came for his first shot.

    The problem is that in some cases I don't have a date of birth just the age the parent has given.

    So I need a function or something that basically calculates age when a date of birth is given and when it isn't given copies age from the "Age at the time of first joining the program". In a different query or the same I'd also like to calculate date of birth where it isn't given.

    I'm thinking I need to use the Nz or IIf function but I'm verrrrryyy new to access so have no idea how this is done.


    Thanks for all the help guys!
    Hi

    I would suggest something like this

    Age: Nz([Age at the time of first joining the program],Int(DateDiff("m",[Table Children]![Date of Birth],[Table Children]![Date of entry])/12))

    This assumes that only one of the two fields in filled in (the other being Null). It also round down the age in years, when calculated.

    Does that help ?


    MTB

  3. #3
    Join Date
    Dec 2009
    Posts
    6
    It WORKED!!!!! (but without the /12), what is the /12 for?
    BUT THANK YOU SO MUCH!!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    taking a wild one the .12 converts an infants age in months to years
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by healdem View Post
    taking a wild one the .12 converts an infants age in months to years
    That sounds about right!!

  6. #6
    Join Date
    Dec 2009
    Posts
    6
    Ahhhh right. How would I calculate the age in weeks? Now that I've got the function in I'm thinking it's kinda redundant to have the age in years because these are EPI (the World Health Organization's Expanded Program on Immunization) vaccines and so are only given to children under two years..

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a suggestion....

    as with any code provided by third parties its always a good idea to investigate the code. if you don't recognise the functions used then look 'em up in the help system.

    there is an easy answer for this, but I'd suggest rather than be spoon fed it you do some investigation of your own. not because of this item, but more to encourage a more analytical approach and learn to make use of the the two best resources you have your disposal your mind and the access help system
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    You couild try looking at DateDiff("ww",DOB,RefDate)

    This calulates the number of week bewteen the RefDate and the DOB.

    This is all in Access Help

    HTH

    If you need more specific info then post back.


    MTB

  9. #9
    Join Date
    Dec 2009
    Posts
    6

    the reverse?

    Thanks for all the help guys! Healdem you're right - this forum is a 'help' not 'spoonfeed' forum lol and I have been trying my best to do as much as I can on my own (I studied History at college so now to be working in IT is a big leap lolll).

    I have one more question though, so how do I use the Nz function to calculate date of birth where I have age (in months).

    so basically I want to generate a new field called DOBcalc or something similar and where if the DOB is already given in the 'Date of Birth' field it should copy it and where the DOB field is blank calculate the DOB by (assumedly) subtracting 'Age at the time of first joining the program' from 'Date of entry' (date of entry is the date at which the child was enrolled in the vaccination program).


    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •