Select Next Earliest Date if Date Field Null

[Laura C.]

Hi there,

I'm trying to create a query that will show the earliest date on which a
person was elected to a particular position.

The SQL currently looks like this:

SELECT tblMaster.tblNames_ID, tblMaster.tblTitles_ID, tblTitles.Seniority,
Min(tblMaster.[First Elected Date]) AS [MinOfFirst Elected Date]
FROM tblTitles INNER JOIN tblMaster ON tblTitles.ID = tblMaster.tblTitles_ID
GROUP BY tblMaster.tblNames_ID, tblMaster.tblTitles_ID, tblTitles.Seniority
ORDER BY tblMaster.tblNames_ID, tblTitles.Seniority;

Some of the date fields (tblMaster.[First Elected Date]) are null because
the election date is unknown. The problem is that the query is returning the
null values instead of the first known election date.

I tried putting an obviously false date (2/22/2999) in all of the null date
fields, but the query doesn't seem to understand this date.

What can I do so that if the date field is null, the query returns the
earliest KNOWN date (i.e., the next earliest date)?

Thanks in advance!