Create Query to evaluate Max Date recognizing Null as High Value

[HoganGroup]

Hi Experts and Fellow Duffers:

I am trying to create a query which will identify the most recent EndDate in tblSchedules associated with tblKids.KidID where a Null value (indicating a current schedule) is the most recent.

Max(EndDate) doesn't recognize Null as the highest value. Last(EndDate) returns the most recently entered schedule but not the most recent EndDate.

Any suggestions? My query so far:

Code: ( sql )

1.
SELECT tblKids.KidID, Min(tblSchedules.StartDate) AS OriginalStart, Max(tblSchedules.EndDate) AS FinalEnd
2.
FROM tblKids INNER JOIN tblSchedules ON (tblKids.KidID = tblSchedules.KidID) AND (tblKids.KidID = tblSchedules.KidID) AND (tblKids.KidID = tblSchedules.KidID)
3.
GROUP BY tblKids.KidID;

Someone suggested to use Nz() but I want to retain the null value. Also, I noted that it fired after the query evaluated Max() so it wasn't much good.

Suggestions greatly appreciated,
Bradley
THANKS!

 

[SkipVought]

Hi,

Since an EndDate can't possibley be greater than today, consider making the null end date, tomorrow's date, for instance, in an IIF() statement.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[HoganGroup]

Thanks for the tip... I did just that and also incorporated a subquery (a first for me) to convert Null to a "FakeEnd" first, evaluate, and then convert it back to Null in the MetaQuery for use! Whaddayaknow, it worked! Here's the end product:

SELECT tblKids.KidID, IIf([FakeEnd]=#12/31/9999#,Null,[FakeEnd]) AS FinalEnd
FROM [SELECT tblKids.KidID, Max(IIf(IsNull([tblSchedules].[EndDate]),#12/31/9999#,[tblSchedules].[EndDate])) AS FakeEnd
FROM tblKids INNER JOIN tblSchedules ON (tblKids.KidID = tblSchedules.KidID) AND (tblKids.KidID = tblSchedules.KidID) AND (tblKids.KidID = tblSchedules.KidID)
GROUP BY tblKids.KidID]. AS [%$##@_Alias] INNER JOIN tblKids ON [%$##@_Alias].KidID = tblKids.KidID
GROUP BY tblKids.KidID, IIf([FakeEnd]=#12/31/9999#,Null,[FakeEnd]);