query to regulate years

[seashore67]

Here is my current coding:

SELECT [Client Data].[Client Name], [Client Data].[Last Date In Program]
FROM [Client Data] INNER JOIN [Post Partnum Info] ON [Client Data].[Client Name] = [Post Partnum Info].[Client Name]
WHERE ((([Client Data].Age)>=20 And ([Client Data].Age)<=25) AND (((Year([Post Partnum Info].[DOB]))+1)<Year(Now())))
GROUP BY [Client Data].[Client Name], [Client Data].[Last Date In Program], [Post Partnum Info].DOB
HAVING ((([Post Partnum Info].DOB)>#1/1/1988#))
ORDER BY [Client Data].[Last Date In Program] DESC;


In the where command I am attempting to add 1 year to the DOB and if that year is less than today's year I want to omit that person. For some reason my current code is not working. Any suggestions to what I've done wrong? Thanks.

Andrew

 

[JonFer]

You are using GROUP BY without any aggregate functions in the SELECT clause. Are you doing this to remove duplicates? If so, use SELECT DISTINCT instead and remove the GROUP BY and HAVING clauses. You also seem to have redundant criteria with checking both the Age and the DOB but maybe there is a reason.

In any event, the WHERE clause should have all of your conditions:

Where [Client Data].Age Between 20 And 25 AND
(Year([Post Partnum Info].[DOB])+1) < Year(Now()) AND
[Post Partnum Info].DOB > #1/1/1988#

If you still don't get any records, remove one criterion at a time to see which is causing the problem. Null values in the Age or DOB field could be a problem.

 

[raskew]

Age is a calculated value--based on DOB and current date (or some other 'as of' date)-- which changes from day to day. It should not be a field in a table, but rather a calculated expression in a query.

There are a variety of approaches to determining age. Here's one that will work well in a query:

Age: DateDiff("yyyy",[DOB],Date())+ _
(DatePart("y",Date())<DatePart("y",[DOB]))

Stipulating that DOB > #1/1/1988# ensures that no results will be returned when looking for folks between 20 and 25. At best, someone born in 1988 can be no more than 16 years old (current date 27-May-04).

HTH - Bob

 

[seashore67]

Age must be a field in the table because the user must know the age of the individual when entering the program, not her current age. DOB is actually a field regarding a person's child so they are non-related items. I haven't had time to implement your code yet Jonfer so I do not know if it works yet. Thanks for your suggestions thus far.

Andrew

 

[seashore67]

Jonfer,

I am getting records using this code:

SELECT DISTINCT [Client Data].[Client Name], [Client Data].[Last Date In Program]
FROM [Client Data] INNER JOIN [Post Partnum Info] ON [Client Data].[Client Name] = [Post Partnum Info].[Client Name]
WHERE ((([Client Data].Age) Between 15 And 17) AND (([Post Partnum Info].DOB)>"#1/1/1988#") AND ((Year([Post Partnum Info].[DOB])+1)<Year(Now())))
ORDER BY [Client Data].[Last Date In Program] DESC;

The records I am attempting to get are those that have the post partnum info.DOB's year no earlier than one year before todays date. This is where I'm having my problem. I do not want entries that persay have a DOB of 2001. Thanks for your help.

Andrew

 

[PHV]

The records I am attempting to get are those that have the post partnum info.DOB's year no earlier than one year before todays date
Something like this ?
WHERE [Client Data].Age) Between 15 And 17
AND [Post Partnum Info].DOB>=DateAdd("yyyy",-1,Now())

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[seashore67]

PHV-

That was the code I was looking for, thank you very much. Could you tell me specifically what the three parameters were so I can use this again in the future. Thanks.

Andrew

 

[PHV]

No F1 key on your keyboard ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244