CALCULATE AGE 2

[villica]

I am wondering if anyone can help me figure how to create an sql statement that will calculate the&nbsp;&nbsp;Age<br><br>I created a query and this is what sql view looks like. I am hoping to create a form with a button an attach the code.<br><br>SELECT DISTINCTROW P.[RECORD ID#], P.CLFNAMES, P.CLLSTNAME, P.ID, P.HEALTHUNIT, L0G.ID,&nbsp;&nbsp;Date()-([P].[DOB]) AS age<br>FROM LOG INNER JOIN P ON (LOG.DOB = P.DOB) AND (LOG.ID = P.ID);<br><br><br>It creates this kind of number 123655<br><br>I would like to be able to convert it a real age like 3.5 and then put it into categories.<br>For example if age is between 3.6 and 5.5 the report should say that there is 1 todler which is the age above.<br><br>Thank you <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[jgarnick]

Not sure if this helps, but here is what I use in my forms to calculate age.<br><br>IIf(IsNull([date_death]),Fix(((Now()-[date_birth])/365.25)),Fix((([date_death]-[date_birth])/365.25)))<br><br>You can put this right in your query--good luck! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[villica]

Thank YOu for your reply I will certainly give it a try, just one question, where do I put this statement. You said in the query do I create a new field and attach this expresion?<br>thanks again<br> <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[jgarnick]

Hello,<br><br>Yes, just type the expression in the query on the field line.&nbsp;&nbsp;I don't know if you deal with date of deaths, if not, just type in ((Now()-[date_birth])/365.25).&nbsp;&nbsp;If you want the age in decimals, you should leave the Fix command out, and set the format for the expression to be fixed and the decimal places to be 1 ??&nbsp;&nbsp;(Right-click on the expression and click on properties.)<br><br>Hope it helps!<br><br> <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[villica]

Thank You I was able to figure it out. Now the only thing is that it gives me&nbsp;&nbsp;the age but for example if a person dob is 8/29/58 . It says that the age is 42, but if fact it should be 41.??? something. How do I change the query so that I will give me the real age.If it is not too much would you please help me. Thank you <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[villica]

Is me again. I got the age to display the way I want it. Since I need to print the report in age categories I decided to design a form with a button. I attached the following code to the button but I am a getting a syntax error but I can' figure it out the error. i have very little knowlege of sql<br><br>Dim strSQL As String<br>&nbsp;&nbsp;'&nbsp;&nbsp;Create SELECT statement.<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>strSQL = &quot;SELECT DISTINCTROW P.[RECORD ID#], P.CLFNAMES,&quot;<br>strSQL = strSQL & &quot;P.CLLSTNAME, P.ID, P.HEALTHUNIT,&quot;<br>strSQL = strSQL & &quot;LOG.HEALTHUNIT, LOG.ID, LOG.HOURS,&quot;<br>strSQL = strSQL & &quot;IIf(IsNull([p].[dob]),(((Now()-[p].[dob])/365.25)),(((Now()-[p].[dob])/365.25))) AS age,&quot;/365.25))) AS age,&quot;<br>strSQL = strSQL & &quot;p.DOB&quot;&quot;&quot;<br>strSQL = strSQL & &quot;FROM LOG INNER JOIN P ON (LOG.DOB = P.DOB) AND (LOG.ID = P.TREATYNO);&quot;<br><br><br>&nbsp;&nbsp;Me!PSubform.Form.RecordSource = strSQL<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub <br><br>I did include my inmediate if statement in the sql. Can this be done<br>The report needs to print as follow<br><br>0 - Infant (0-3.5 years)&nbsp;&nbsp;&nbsp;&nbsp;2<br>1 -Toddler(3.6-5.5)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 etc<br><br>so I like to have a case statement after the sql . Any suggestions <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[gol4]

another option<br>Age: Int(DateDiff(&quot;y&quot;,[bdate],date()/365.25))

 

[gol4]

sorry about first post should be<br>Age: Int(DateDiff(&quot;y&quot;,[bdate],Date())/365.25)

 

[DougP]

jgarnick,<br>Make this an FAQ<br>several people over so many months have asked for the Age thingy<br>Great on-liner by the way. <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[jgarnick]

On-liner?&nbsp;&nbsp;Or one-liner?&nbsp;&nbsp;What was it?&nbsp;&nbsp;I must have missed something.... <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>