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

[nkk]

Hey guys,
I'd posted earlier about creating a database for a vaccination clinic is a slum. All the responses were really helpful (thank you!) but I'm encountering some further problems. 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!

[ChrisPadgham]

iif(isnull([Table Children]![Date of Birth]),[Table Children].[Age At Entry],DateDiff("m",now(),[Table Children]![Date of Birth]))

[nkk]

iif(isnull([Table Children]![Date of Birth]),[Table Children].[Age At Entry],DateDiff("m",now(),[Table Children]![Date of Birth]))

Hey Chris,
Thanks so much for the help! So how do I switch the formula around i.e. now I want to calculate Date of Birth where I don't have it. I want to create a query that basically copies Date of Birth where it is given and where the Date of Birth field is null it subtracts the child's age (retrieved from Age at the time of first joining the program) from the Date of entry (i.e. the day the child joined the program) – i'm assuming that will give me the DOB. Umm will it?

[ChrisPadgham]

I had a remarkably similar situation writing an app for a charity supporting single mothers to work out their childrens birthdays.

The difficulty is that it will give you a date in their birth year but not their actual birthday, so if you store it in that field you need some way of indicating that it is an approximation and not the actual date. If you choose a fixed date in the year, say 1st Jan, then at least your users will know that this has been estimated and not the actual birthday.

Me.DOB = "1/1/" & datediff("YYYY",me.age*-1,me.dateofEntry)

[nkk]

thanks!