Query to return records for a given year (1 Viewer)

[Jkittle]

Okay I've searched the forum and have not found exactly what I'm looking for.

I'm trying to create a query to show me records for a given year. The issue I'm having is that each record has (4) dates fields and each record can contain null values.

Is it possible to do this in a query with data like the example below?

Field 1 ID (1), Field 2 Date (12/22/2012), Field 3 Date (2/06/2013), Field 3 Date (Null), Field 4 Date (Null)

In this example I would want 2013 data but would need to be able to search any year. I could also have dates in all (4) fields

Thank for your help
Jerry

 

[sensetech]

Something like

select <whatever> from <table> where year(nz(Field1,'1 Jan 1899')) = pYear or year(nz(Field2,'1 Jan 1899')) = pYear or year(nz(Field3,'1 Jan 1899')) = pYear or year(nz(Field4,'1 Jan 1899')) = pYear

where pYear is the year for which you want data and it won't be 1899!

 

[namliam]

no need for the nz function, but all the rest is spot on

 

[sensetech]

Just checked namliam's point - he's quite right. That's what I get for answering off the top of my head rather than testing my theories