Pulling records that has a null end date

[dkellam]

I am writing a report that I am trying to pull current records into. That which makes them current is a blank end date. Is there a formula to accomadate that? I current have to following formula in the Select Expert:

{POLLUTANT_L.POLLUTANT_DESC} = "Zinc, total" and
{LIMIT_TYPE_L.LIMIT_TYPE_DESC} in ["Monthly Avg", "Daily Max"] and
{ADDRESS.ADDRESS_L_S} = 2.00 and
{ADDRESS.CITY} = "Chesapeake" and
{FACILITY.INACTIVE} = "N" and
isnull ({LIMIT_SET_DATES.END_DATE})

The last line is causing my report to run blank. Everything else is working properly

 

[raja2008]

iam not sure but may be you should try like this:

if isnull({LIMIT_SET_DATES.END_DATE}) = true then

{POLLUTANT_L.POLLUTANT_DESC} = "Zinc, total" and
{LIMIT_TYPE_L.LIMIT_TYPE_DESC} in ["Monthly Avg", "Daily Max"] and
{ADDRESS.ADDRESS_L_S} = 2.00 and
{ADDRESS.CITY} = "Chesapeake" and
{FACILITY.INACTIVE} = "N"
else " " i dont know if this works
or try supressing the details based on the end date
isnull({LIMIT_SET_DATES.END_DATE}) = false

 

[Madawc]

not isnull ({LIMIT_SET_DATES.END_DATE})

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[dkellam]

Thank you for the suggestions, I can not get it to work properly. I am using CRXI. Is there I way that I can tell CR to pull the most recent start date? That is another way of retrieving the records I want.

 

[lbass]

You need to determine whether your date field is ever null. It looks like it isn't, so you should determine what the default is, by placing the field in the detail section and running the report to see how it displays.

You could try:

isnull({LIMIT_SET_DATES.END_DATE}) or
{LIMIT_SET_DATES.END_DATE} = date(0,0,0)

-LB