How to return Max date or Null (if there is a Null present)

[David Ball]

Hi,

I have a query where I Group By an Equipment Number field and then, for each equipment Number, return the Max (latest) Forecast Delivery date.

The problem is where one or more entries for a given Equipment Number has a Null in the Forecast Delivery date field. In these cases I need to return Null.

So, I need something like: "Return Max if there is no Null, otherwise return Null" for my Forecast Delivery field.

I have no idea how to do this Access so any help would be greatly appreciated.

Thanks very much.

Dave

 

[John W. Vinson]

Hi,

I have a query where I Group By an Equipment Number field and then, for each equipment Number, return the Max (latest) Forecast Delivery date.

The problem is where one or more entries for a given Equipment Number has a Null in the Forecast Delivery date field. In these cases I need to return Null.

So, I need something like: "Return Max if there is no Null, otherwise return Null" for my Forecast Delivery field.

I have no idea how to do this Access so any help would be greatly appreciated.

Thanks very much.

Dave

Try putting in a calculated field:

NZ([Forecast Delivery], #12/31/9999#)

That's the largest date Access can represent - if it's returned by your Max
query, you know that there's a NULL in the record.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David Ball]

Hi John,

Thanks very much for that but if there is a Null I need to return Null. And return the Max (latest) date if there is no Null.

 

[David Ball]

Hi John,

Thanks for the tip. I just replaced #12/31/9999# with "Date TBA" and that has given me exactly what I need.

Thanks again.

Dave

 

[Bob Barrows]

Hi John,

Thanks very much for that but if there is a Null I need to return
Null. And return the Max (latest) date if there is no Null.

He provided the first step. Here's the rest

Iif(Max(NZ([Forecast Delivery], #12/31/9999# )) = #12/31/9999#,Null,
Max(NZ([Forecast Delivery], #12/31/9999# )) )