Query based on operator defined YEAR from an existing date

[tdsutter]

I have a query that lists various attributes of a group of individuals. One of the attributes is the date of death. For most of the records, that field is null. However, if the individual has died, the operator inserts the date of death in the form of mm/dd/yyyy. Now, I want to create a query based that the aforementioned query that, when executed, asks the operator for a year (What year to report?), then locates only the records with a date of death in the requested year. I've been working in the second query using the design view. I know how to ask the question. I don't know how to compare the operator's response to the year of the date of death.

Looking forward to suggestions. Thanks.

[JoeM]

Create a calculated field in your query that returns the year from the date of death, i.e.

YearOfDeath: Year(Nz([DateOfDeath],DateValue("12/31/2099")))

Then, you can make this a parameter query by by placing this under the Criteria row of this new calculated field:
[Enter Year of Death]

Then, anytime you run the query, it will prompt you to enter the year of death, and return all the appropriate records.

[tdsutter]

Thanks. Works like a charm. BTW, why did you choose 12/31/2099 as the argument for DateValue?

[June7]

Just pick any date far enough into future that it will always be greater than any value in the field. 2099 gives the database a life of 86 years before that expression would have to be modified. This works as well:

YearOfDeath: Year(Nz([DateOfDeath], #12/31/2099#))

Actually, with a date of death field, next year is probably far enough - DateAdd("yyyy",1,Date()) - never have to change the expression, is perpetual.

[Dal Jeanis]

June - using tomorrow's date is highly inadviseable. The report might get printed, you know, and the operator might ask for this year.

DateAdd 30 years sounds good to me.

[June7]

Sorry, I just realized that and editing to say 'next year' and fix calc. If they filtered on the entire date value, tomorrow should be far enough.

[JoeM]

Thanks. Works like a charm. BTW, why did you choose 12/31/2099 as the argument for DateValue?

I just chose some random date way out in the future in the event that the Date of Death is not populated, because that will probably return errors.
Note the usefulness of the NZ function. Basically, you use it to say what you want to return in the event of a Null value.

[tdsutter]

WOW! Thanks everybody for all the help. Much appreciated.