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

1. Registered User
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. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Originally Posted by nkk
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. Registered User
Join Date
Dec 2009
Posts
6
It WORKED!!!!! (but without the /12), what is the /12 for?
BUT THANK YOU SO MUCH!!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
taking a wild one the .12 converts an infants age in months to years

5. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Originally Posted by healdem
taking a wild one the .12 converts an infants age in months to years
That sounds about right!!

6. Registered User
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..

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

8. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
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. Registered User
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
•