To find a specific date

[jean2002]

Hello,

I have a query with the following structure:

qryAllPersons:

Select tblPersons.ID, tblPersons.Name, tblPersons.DateRecorded_A, tblPersons.DateRecorded_B, tblPersons.DateRecorded_C, tblPersons.DateRecorded_D,
From tblPersons;

And this gives the following result when executed:

ID---NAME-----DateA------DateB------DateC------DateD
1----John---26/10/1999
2----Peter-------------25/09/2001------------31/12/2000
3----Steve-------------------------18/3/2003
4----Jane-------------------------22/5/2004---4/4/2004

What I would like to do now is create a query (based on the above) which would always take the first available date in the following order: DateA->DateB->DateC->DateD. I.e. if no date is available for DateA, take DateB etc. It should then return only the Year of the date.

I would like the following result for the above 4 records:

1999
2001
2003
2004

I tried using this SQL code so far but nothing comes from it, it is asking me to enter parameter values when I execute it – which I don’t think it should! Please help me and suggest a correction to this code:

SELECT IIf(nz(Year([qryAllPersons]![DateA]))<>0,
Year([ qryAllPersons]![DateA]),
IIf(nz(Year([qryAllPersons]![DateB]))<>0,Year([qryAllPersons]![DateB]),
IIf(nz(Year([qryAllPersons]![DateC]))<>0,Year([ qryAllPersons]![DateC]),Year([qryAllPersons]![DateD])))) AS LatestYEAR
FROM qryAllPersons;

Regards,
Jean

 

[jean2002]

Sorted it out already, there was a space in two places in the query for [ qryAllPersons]