Calculate Age In Full Years if Over 2 years old and Months if Under 2 years old (1 Viewer)

[JJFernandez]

Hi all,

I would like to populate the age of a person in an unbound control in an access form (also a query) by comparing Now to dob. I can calculate full years or full + partial years in decimal format. I'm wondering if anyone has a tip on how I could produce a result of a full year if over 2 years of age and a value in months ONLY IF the age is less than 2 while also adding a text string of "Months" into the text box. I'm trying to make the result more practical than ie/ 0.4565 years.

Apologies in advance if this has been asked and answered. I did try to search for it but didn't find quite what I was looking for.

Many thanks,

Jeremy

 

[theDBguy]

Hi Jeremy. You should be able to use an IIf() statement to display the correct age. Are you using a custom function to calculate the age?

 

[JJFernandez]

Thanks for the reply. So far I've just used the DateDiff expression to calculate full years within the text box.

So I could create an if then statement saying some like if (DateDiff("yyyy",[dob], Now()))<2 then (DateDiff("m",[dob], Now())&"Months" or something similar? Forgive my poor syntax I don't do this often...

 

[Micron]

Maybe

IIF(datediff("m",#01/01/2010#,Date)<24,datediff("m",#01/01/2010#,Date) & " months",datediff("yyyy",#01/01/2010#,Date) & " years")

as the textbox control source. Or you can assign a function (yes, with an IF Then block) if that looks too messy. You would replace the hard coded dates above with your own data.

 

[JJFernandez]

Thanks for that, looks promising. I'll give it a go and report back...

 

[JJFernandez]

That worked great - Thank you!

 

[theDBguy]

That worked great - Thank you!

Hi. Glad to hear you got it to work using the IIf() function. Good luck with your project.

 

[Micron]

If that is directed to me, then it's another notch in my gun! I think that makes 2.