age greater than 18

[puthenveetil]

Hi all,

In my db I had a table for students. I am using a report to view the age of the students. I want to list the name of the students who are 18 or more compared to the current date. The code which I am using now is

SELECT Recipients.Inst_Name, Recipients.Rec_First, Left((Now()-[dob])/365,InStr((Now()-[dob])/365,'.')-1) AS age
FROM Recipients
WHERE (((Left((Now()-[dob])/365,InStr((Now()-[dob])/365,'.')-1))=18 Or (Left((Now()-[dob])/365,InStr((Now()-[dob])/365,'.')-1))>18))
ORDER BY Recipients.Rec_First;


i want to know whether this code is ok or If you had any idea about this please give me a solution

Thanks in adance

Thanks

 

[Mile-O]

That won't work because not every year is 365 days long.

Put this in a standalone module:

Public Function CalculateAge(ByVal dteDOB As Date, Optional SpecDate As Variant) As Integer
    Dim dteBase As Date
    Dim intCurrent As Date
    Dim intEstAge As Integer
    If IsMissing(SpecDate) Then
        dteBase = Date
    Else
        dteBase = SpecDate
    End If
    intEstAge = DateDiff("yyyy", dteDOB, dteBase)
    intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
    CalculateAge = intEstAge + (dteBase < intCurrent)
End Function

You can now change your query to:

SELECT Recipients.Inst_Name, Recipients.Rec_First, CalculateAge([dob]) AS age
FROM Recipients
WHERE CalculateAge([dob]) >= 18
ORDER BY Recipients.Rec_First;

 

[daveUK]

Why not use the DateAdd function?

SELECT Recipients.Inst_Name, Recipients.Rec_First AS age
FROM Recipients WHERE ([dob]<=DateAdd("yyyy",-18,Date()) ORDER BY Recipients.Rec_First;

You might have to tweak the SQL statement a bit, but it seems much easier than using a function AND a SQL statement.

HTH

 

[Mile-O]

Why not use the DateAdd function?

Because the question clearly asks to view the age of the students.

 

[daveUK]

I am using a report to view the age of the students.

oops

Got a bit excited as I thought I had an easier answer

 

[puthenveetil]

Hi,

SJ, That worked .....Thanks guys for the solution......


Thanks