Query results for age under 1 year? 1

[lucybelle]

I'm using MS Access 97. This is the formula that I'm using in a query to calculate the age of a client:

ClientAge:Int(DateDiff("m",[tblgloclients]![ClientDOB],
Date())/12)

If the age is under 1 year, I get 0. How can I get the number of months when this is the case? Thanks.

 

[PHV]

Brute force method:
ClientAge:IIf(Date()-[tblgloclients]![ClientDOB]>365, Int(DateDiff("m",[tblgloclients]![ClientDOB],Date())/12), DateDiff("m",[tblgloclients]![ClientDOB],Date()))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lucybelle]

That worked great! Thanks! How can I get it to show "months" when the age is less than 1?

 

[lucybelle]

That worked great! How can I get it to show "months" when the age is less than 1?

 

[lucybelle]

That worked great! How can I get it to show "months" when the age is less than 1?

 

[PHV]

ClientAge:IIf(Date()-[tblgloclients]![ClientDOB]>365, Int(DateDiff("m",[tblgloclients]![ClientDOB],Date())/12) & " Year(s)", DateDiff("m",[tblgloclients]![ClientDOB],Date()) & " Month(s)")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lucybelle]

Works Perfect!!! Thanks so much for your help.

 

[lucybelle]

Works Perfect!!!!! Thanks for your help.
[Smile2][Smile2][Smile2][Smile2][Smile2]