Running query about date

[Guest]

Hello,
Say for example I have a database containing the date of birth of people in
the format of YYYYMMDD (a string/text field) and another field about the age
of the person (a numerical field) and another field is, say, the date they
had their birthday last year. I would like to check whether the year in the
date of birth field and age adds together is the year in the date they had
the last birthday field (or off by no more than 2 years), and give me the
list of the people who are not. Is it possible to do that by writing a query
or I have to use a macro and write an SQL statement for it? In both case, how
should that be done?

Thanks a lot!

 

[John Spencer]

This expression should return true if the difference is greater than plus or
minus 2.

Abs(Val(Left([DOB],4)) + Age - Year(LastBirthday) )> 2

In the query grid
Field: Expr1: Abs(Val(Left([DOBstring],4)) + Age -
Year([LastBirthdayDate]) )
Criteria: > 2
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Three problems.

1. You have their DOB in a text field and not a date field.

2. You have a derived value, their age, also stored in the table.

3. You have a birthday field which is also a derived field UNLESS you are
talking about the day they celebrated their birthday and not the actual birth
date.

If it were me, I'd convert the DOB field to a Date datatype. Something like
below will tell you if you have dirty or good data for the conversion.
Replace the DOB with the actual field name.

IsDate(mid([DOB], 5,2) & "/" & right([DOB], 2) & "/" & left([DOB], 4))

If you don't get any Falses, you can change IsDate to CDate to convert the
data to a date field.

After that you can readily find both the person's age and birthday with only
one field. The age is a little tricky; however, there are plenty of code
examples if you do a Google search.

The fix:

 

[Guest]

John's answer did the trick, thanks!

This expression should return true if the difference is greater than plus or
minus 2.

Abs(Val(Left([DOB],4)) + Age - Year(LastBirthday) )> 2

In the query grid
Field: Expr1: Abs(Val(Left([DOBstring],4)) + Age -
Year([LastBirthdayDate]) )
Criteria: > 2
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,
Say for example I have a database containing the date of birth of people
in
the format of YYYYMMDD (a string/text field) and another field about the
age
of the person (a numerical field) and another field is, say, the date they
had their birthday last year. I would like to check whether the year in
the
date of birth field and age adds together is the year in the date they had
the last birthday field (or off by no more than 2 years), and give me the
list of the people who are not. Is it possible to do that by writing a
query
or I have to use a macro and write an SQL statement for it? In both case,
how
should that be done?

Thanks a lot!