By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

Query Filter Question

P: 4
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
2 Weeks Ago #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,024
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:
Expand|Select|Wrap|Line Numbers
  1.  DateAdd("YYYY",70,JP_tbl.[DOB]) AS Expr1
Also DATEDIFF(..) will give you the age of a person:
Expand|Select|Wrap|Line Numbers
  1. DateDiff("yyyy",JP_tbl.DOB, Now()) AS Age
2 Weeks Ago #2

Expert Mod 5K+
P: 5,327
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...
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:

Expand|Select|Wrap|Line Numbers
  1. Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
  2.     'Purpose:   Return the Age in years.
  3.     'Arguments: varDOB = Date Of Birth
  4.     '           varAsOf = the date to calculate the age at, or today if missing.
  5.     'Return:    Whole number of years.
  6.     Dim dtDOB As Date
  7.     Dim dtAsOf As Date
  8.     Dim dtBDay As Date  'Birthday in the year of calculation.
  10.     Age = Null          'Initialize to Null
  12.     'Validate parameters
  13.     If IsDate(varDOB) Then
  14.         dtDOB = varDOB
  16.         If Not IsDate(varAsOf) Then  'Date to calculate age from.
  17.             dtAsOf = Date
  18.         Else
  19.             dtAsOf = varAsOf
  20.         End If
  22.         If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
  23.             dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
  24.             Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
  25.         End If
  26.     End If
  27. End Function
2 Weeks Ago #3

Expert 100+
P: 1,024
@zmbd: Thanks for details on how to calculatie age more precise..
2 Weeks Ago #4

Expert Mod 15k+
P: 31,302
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 :
Expand|Select|Wrap|Line Numbers
  1. (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().
2 Weeks Ago #5

P: 4
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)
2 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.