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:

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

Thanks,
Bradley

[Scott Price]

Try ORDER BY tblKids.EndDate...

Welcome to the Scripts!

Regards,
Scott

[nico5038]

When you see Null as the highest enddate, you should use the NZ() function e.g. like:

Max(Nz(EndDate,#01-01-2999#)) 
 

Getting the idea ?

Nic;o)

[HoganGroup]

Thank you both you both for you replies. Both the ORDER BY and Nz() suggestions were good but occurred after the query evaluated Max() so I was still left with the max non-null date (which disregards current schedules).

After playing, I got to the following partial solution which uses iif (instead on Nz) to create a "FakeEnd" for Null rows and an additional field to put them back to Null (which would be the one field I would use.) Technically it works but prompts the user for a "FakeEnd" parameter before figuring it out which really isn't going to work practically.

I have a thought that a subquery (to make FakeEnd) might be appropriate here but I've reached the limit of my expertise with regard to queries, let alone subqueries. If either/any of you think a subquery might work, what would it look like?

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

Thanks,
Bradley

[nico5038]

I posted:

Max(Nz(EndDate,#01-01-2999#))

and NOT

NZ(Max(EndDate,#01-01-2999#))

The problem described about the Max can't happen with the proposed Max(NZ()) statement!

Nic;o)

[HoganGroup]

Thanks to you both for your help. It was Max(Nz(... that I tried but with the same result. Subquerying did work, though. Hooray! In the end, this is the SQL that worked.

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]);

Thank you again,
Bradley