Following will return empty date if there are no records with DeedType = "LC", just tested.
NVL(MIN(IIF(DeedType = "LC", date, Null)), {}) AS MinLCActDate,
>>Did you try Null?
>
>How should I try it? BTW, he wants blank, not nulls. Min(date(),NULL) returns NULL.
>
>>>Ok, I discussed this problem, and he wants blank dates, if where are no LC records in a file. So, unfortunately, this problem could not be solved in one select. It now becomes much more complicated... :(
>>>
>>>>The only problem I can see with this code, that you'll get DATE(9999,12,31) as a MinLCActDate if there're no records with DeedType = "LC". You can try to use Null instead of DATE(9999,12,31) to fix it (not tested).
>>>>
>>>>>Exactly :) I figured this out by myself (decided to finally eat lunch). Anyway, three stars for you (I knew, you would be the first to answer :))
>>>>>
>>>>>>Something like
MIN(IIF(DeedType = "LC", date, DATE(9999,12,31)) AS MinLCActDate,
>>>>< snip >
--sb--