Querying People Greater than 15 Years Old

[Haji]

Hi,

We are sending out letters to our customers on a weekly
basis and only want to send to people who are 16 years or
older. I have a field called DateOfBirth with the value
01/05/88. How would I exclude this person from my query
now but include him when he turns 16 in January?

Thanks,

Haji

 

[Brian Camire]

You might put a criteria like this on your DateOfBirth field:

<=DateAdd("yyyy", -16, Date())

 

[John Vinson]

Hi,

We are sending out letters to our customers on a weekly
basis and only want to send to people who are 16 years or
older. I have a field called DateOfBirth with the value
01/05/88. How would I exclude this person from my query
now but include him when he turns 16 in January?

Put a calculated field in your Query:

AGE: DateDiff("yyyy", [DateOfBirth], Date()) -
IIF(Format([DateOfBirth], "mmdd") > Format(Date(), "mmdd"), 1, 0)

DateDiff calculates the age in whole years; the IIF statement corrects
this value by subtracting a year if the person's birthday has not
arrived.

Use a criterion on this field of > 15 and you'll have the desired
result.

 

[Haji]

Brain,

Thanks for your help. I am encountering an unusual
problem. My datefield BirthdayQuery6.Birthday is
actually a text field which I can not change in my
original table. I am using the statement CDate
([Birthday]) as [Date] to make it a text field. I am
then using your where clause to get everyone over 15.
The query runs fine and about two seconds after I get
results, I get an error message saying "Data Type
Mismatch in Criteria Expression". Then every field has a
#Name? in it. Any thoughts as to what is causing this?

Thanks,

Haji



SELECT BirthdayQuery6.Name, BirthdayQuery6.Birthday, CDate
([Birthday]) AS [Date]
FROM BirthdayQuery6
WHERE (((CDate([Birthday]))<=DateAdd("yyyy",-16,Date())));

 

[Brian Camire]

You would get this error if the text in the [Birthday] field in one or more
records cannot be converted to a valid date by CDate.

Brain,

Thanks for your help. I am encountering an unusual
problem. My datefield BirthdayQuery6.Birthday is
actually a text field which I can not change in my
original table. I am using the statement CDate
([Birthday]) as [Date] to make it a text field. I am
then using your where clause to get everyone over 15.
The query runs fine and about two seconds after I get
results, I get an error message saying "Data Type
Mismatch in Criteria Expression". Then every field has a
#Name? in it. Any thoughts as to what is causing this?

Thanks,

Haji



SELECT BirthdayQuery6.Name, BirthdayQuery6.Birthday, CDate
([Birthday]) AS [Date]
FROM BirthdayQuery6
WHERE (((CDate([Birthday]))<=DateAdd("yyyy",-16,Date())));

-----Original Message-----
You might put a criteria like this on your DateOfBirth field:

<=DateAdd("yyyy", -16, Date())




.