Query Filter Question

[10geoff732]

From my previous question (which I greatly appreciated the answer).

I have now added a tbl named JP_tbl. It has 4 fields.
Name, (typical name ie Joe Bloggs)
DOB, (01/01/2001)
Date expiry in date format (01/01/2001)+ 70 years
Completed (True/ False) Tick Box

What I need to do is filter out the records where by the DOB (date of birth) is calculated to give a result for Date expiry plus 70 years. Once that date is reached it is removed from the qry

Also where completed true/false would further filter the results.

Sounds ominous but work has certain rules where by anyone plus 70 years can not make certain decisions for legal requirements.
Maybe via a Expr:[...... ??

Can I run this straight from the qry?

Thanks in advance

[Luuk]

I do think that you do not need the field 'Date expiry'. When it's always 70 years after DOB, than this expression will do it:

 DateAdd("YYYY",70,JP_tbl.[DOB]) AS Expr1

Also DATEDIFF(..) will give you the age of a person:

DateDiff("yyyy",JP_tbl.DOB, Now()) AS Age

[zmbd]

I 2nd Luuk's opinion that the expry field is unneeded IF and only if it will always be 70+ years from DOB. It's a good rule of thumb not to store information that can be calculated from the data in the table.

As for calculating the age, there are some minor issues that can arise, and I have had the following happen to me...

http://allenbrowne.com/func-08.html
Age() Function

Given a person's date-of-birth, how do you calculate their age? These examples do not work reliably:
Format(Date() - DOB, "yyyy")
DateDiff("y", DOB, Date)
Int(DateDiff("d", DOB, Date)/365.25)

DateDiff("y", ..., ...) merely subtracts the year parts of the dates, without reference to the month or day. This means we need to subtract one if the person has not has their birthday this year. The following expression returns True if the person has not had their birthday this year:
DateSerial(Year(Date), Month(DOB), Day(DOB)) > Date

True equates to -1, so by adding this expression, Access subtracts one if the birthday hasn't occurred.

The function is therefore:

Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
    'Purpose:   Return the Age in years.
    'Arguments: varDOB = Date Of Birth
    '           varAsOf = the date to calculate the age at, or today if missing.
    'Return:    Whole number of years.
    Dim dtDOB As Date
    Dim dtAsOf As Date
    Dim dtBDay As Date  'Birthday in the year of calculation.
 
    Age = Null          'Initialize to Null
 
    'Validate parameters
    If IsDate(varDOB) Then
        dtDOB = varDOB
 
        If Not IsDate(varAsOf) Then  'Date to calculate age from.
            dtAsOf = Date
        Else
            dtAsOf = varAsOf
        End If
 
        If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
            dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
            Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
        End If
    End If
End Function

]

[Luuk]

@zmbd: Thanks for details on how to calculatie age more precise..

[NeoPa]

If you're actually interested in whether or not someone has reached their 70th birthday than calculating their age would be irrelevant and just give you extra troubles.

The part of the WHERE clause that would exclude records where the subject is >=70 would be :

(Date()>=DateAdd('yyyy',70,[DOB]))

This assumes you are checking it against today's date. If other dates are required then specify that in place of Date().

[10geoff732]

WOW!! Such great and quick help, very much appreciated. Hadn't even thought about not needing a expiry field and just using above but makes total sense and now slapping myself as to why I thought I needed it in the first place.

Greatly appreciated for everyones help on this! Ill give it a crack when I get back to work tomorrow (Don't have Access at home)