By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Create Query to evaluate Max Date recognizing Null as High Value

P: 8
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:

Expand|Select|Wrap|Line Numbers
  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;
  4.  
Thanks,
Bradley
Oct 7 '07 #1
Share this Question
Share on Google+
5 Replies


Scott Price
Expert 100+
P: 1,384
Try ORDER BY tblKids.EndDate...

Welcome to the Scripts!

Regards,
Scott
Oct 7 '07 #2

nico5038
Expert 2.5K+
P: 3,072
When you see Null as the highest enddate, you should use the NZ() function e.g. like:
Expand|Select|Wrap|Line Numbers
  1. Max(Nz(EndDate,#01-01-2999#)) 
  2.  
Getting the idea ?

Nic;o)
Oct 7 '07 #3

P: 8
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?

Expand|Select|Wrap|Line Numbers
  1. 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
  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, IIf([FakeEnd]=#12/31/9999#,Null,[FakeEnd]);
  4.  
Thanks,
Bradley
Oct 7 '07 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 8 '07 #5

P: 8
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblKids.KidID, IIf([FakeEnd]=#12/31/9999#,Null,[FakeEnd]) AS FinalEnd
  2. FROM [SELECT tblKids.KidID, Max(IIf(IsNull([tblSchedules].[EndDate]),#12/31/9999#,[tblSchedules].[EndDate])) AS FakeEnd
  3. FROM tblKids INNER JOIN tblSchedules ON (tblKids.KidID = tblSchedules.KidID) AND (tblKids.KidID = tblSchedules.KidID) AND (tblKids.KidID = tblSchedules.KidID)
  4. GROUP BY tblKids.KidID]. AS [%$##@_Alias] INNER JOIN tblKids ON [%$##@_Alias].KidID = tblKids.KidID
  5. GROUP BY tblKids.KidID, IIf([FakeEnd]=#12/31/9999#,Null,[FakeEnd]);
Thank you again,
Bradley
Oct 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.