Select Earliest Date Entered if Date Field 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. In these cases, 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!

 

[John Spencer]

First question is - are you sure this is a field that is a datetime
field? It sounds as if this could be a text field that contains a date
string. If the field is a text field it could contain a zero-length
value (not a null) and then Min would return the zero-length string as
the minimum.

If the value is a zero-length string, you can try a where clause.

You still won't get correct results if this is a string field unless you
modifiy the min field to read
Min(CDate([First Elected Date]))

So making both those changes should give you the desired result if the
field is actually a text field holding a string that is a date.

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



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================