Display last record in group except when null 1

[RenaG]

Am working in Crystal Reports 10

I am trying to display on my report the last medicine record. I got a formula that goes in the Report Select|Group for doing that off this forum but the problem is that some of the records have a blank or I assume Null date. Using this formula works except that these blank/Null records don't show up:

{@StartDate} = maximum({@StartDate},{Drug.Drug_Label})

I tried using this:

If IsNull({@StartDate}) then
   {@StartDate}
Else
   {@StartDate} = maximum({@StartDate},{Drug.Drug_Label});

But get an error that says "A date is required here" and the statement after the Else is highlighted.

Any suggestions on how I display the last record or blank/Null record?

TIA!
~RLG

 

[lbass]

Please always show the content of formulas when you post, i.e., for {@StartDate}.

-LB

 

[Charliy]

Basically you're telling it, If Null then Null. The way you've got it set up you'd need some other default date to replace {@StartDate} if it is null.

Pick some date like currentdate-30 and plug that into your formula

If isnull({@Startdate}
then currentdate-30
else . . .

just to see if that makes it work.

 

[RenaG]

You're right, of course, LB. I was hoping to make it less confusing but leaving out information isn't helpful either.

SQL Expression: OtherMed
ISNULL(dbo.fn_Testmask(PharmOrd.Component_Type,2),'')

This is a canned function that we get with the system. The documentation we got says

Checks to see if the bitmap of a field is set.

Formula Field: StartDate
if {%OtherMed} <> 2 then
//if order print Order Start_DtTm
Date({Orders.Start_DtTm})
else
//if Other Med print Other Med Start_Outside_DtTm
Date({PharmOrd.Outside_Start_DtTm})

HTH!

~RLG

 

[lbass]

I think you need to check for a null in your {@StartDate} formula:

if {%OtherMed} <> 2 and
not isnull({Orders.Start_DtTm}) then
Date({Orders.Start_DtTm}) else
Date({PharmOrd.Outside_Start_DtTm})

You can try a group selection formula like this:

isnull({@StartDate}) or
{@StartDate} = maximum({@StartDate},{Drug.Drug_Label})

...and this would return all records where startdate is null or the most recent record. But of course, if all records must have one or the other start date and they both are null, there would be no record to see.

-LB

 

[RenaG]

Thanks, LB. You are such a blessing!

I need to see the medication records with a null start date. I took the group selection formula you suggested and it works perfectly (as usual).

Have a wonderful day!

~RLG