Help With a Query - Datepart

[zeusads]

Hello Everyone,

I'm just trying to create this query, I think I'm almost there but can't find what is wrong or what to do.

Imagine a table with these fields:

Animal_name
Arrival_Date
Death_Date

What I want is to create a query to grab how many animals were alive in a specific year.
For example, let's say it is the year of 2000.

It needs to exclude all animals that arrived after this year.
It needs to exclude all animals that died until the end of the year 2000.

What I did was create a Field on the Query like this: ArrivalYear: DatePart("yyyy";[Arrival_Date])
And another one like this: DeathYear: DatePart("yyyy";[Death_Date])



With these two I Can solve the 2 first problems. But If an animal is still alive he will not be on the list, and it needs to.
The problem is when you search for some year and in that year you have an animal that has no Death_Date it will not be shown on the results.

If anyone knows a way to do that I'll be very grateful.

Thanks a Lot

[Bob Fitz]

What criteria do you use in the calculated "DeathYear" column

[zeusads]

What criteria do you use in the calculated "DeathYear" column

<=[Type the Year] in ArrivalYear and DeathYear

[Micron]

Try

SELECT * FROM tblYourTable WHERE Arrival_Date = DatePart("yyyy";[Arrival_Date]) As ArrivalYear AND Death_Year = DatePart("yyyy";[Death_Date]) As Death_Year
UNION
SELECT * FROM tblYourTable WHERE Arrival_Date = DatePart("yyyy";[Arrival_Date]) AND [Death_Date]) Is Null;

You might get prompted for two Arrival Dates though. You could work around that by using a form, but someone else may have a better approach.

[Bulzie]

In query window in criteria on the same row so it does an AND for those 2 fields, would it be:

ArrivalDate: < #1/1/2001#
DeathDate: Null or >#12/31/2000#