Select Earliest Date Entered if Date Field is Null

[Laura C.]

Hi,

I'm trying to build a query that will show me the first date on which a
person was elected to a particular position in the company.

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;


The problem is that some of the date fields (tblMaster.[First Elected Date])
are null, since the election date is unknown. When I run the query, I get the
null value as the "Min" instead of the first known date.

I tried entering a patently false date (2/22/2222) -- one much later than
any conceivable election date -- in all of the blank rows, but the query does
not seem to be recognizing this date.

Can you help me rewrite so that if the date field is null, the query returns
the earliest date that has been entered (i.e., the next earliest date)?

Thanks in advance!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure this is what you want, but...

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

WHERE tblMaster.[First Elected Date] IS NOT NULL

GROUP BY tblMaster.tblNames_ID, tblMaster.tblTitles_ID,
tblTitles.Seniority
ORDER BY tblMaster.tblNames_ID, tblTitles.Seniority;

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZTH+oechKqOuFEgEQKEWACg4uv7kQPbPoWyM/YMGFpXw7Su4gUAn1vr
vibzg+BpZVyKuocDW2VM4dCT
=9Qdo
-----END PGP SIGNATURE-----