Select expert not returning all needed values

[silck]

Hello,

In the select expert, I have the following:

{benefit.tax_year} = (left(cstr(year({?enddate})),1) + mid(cstr(year({?enddate})),3,3))
or
isnull({BENEFIT.TAX_YEAR})


{benefit.tax_year} is a string value

When running the report, I am using the {?enddate} of 9/30/2004, so the report is only pulling the 2004({BENEFIT.TAX_YEAR}). If I reverse it to:

isnull({BENEFIT.TAX_YEAR})
or
{benefit.tax_year} = (left(cstr(year({?enddate})),1) + mid(cstr(year({?enddate})),3,3))

It only pulls the null values and I need to it to pull both.

Any suggestions?

 

[lupins46]

left(cstr(year({?enddate})),1) + mid(cstr(year({?enddate})),3,3))

This expression would return 204 from a date of 9/30/2004

Is this what you intend?

 

[silck]

In my report, that formula is pulling the 2004.

 

[kskid]

Instead of

{benefit.tax_year} = (left(cstr(year({?enddate})),1) + mid(cstr(year({?enddate})),3,3))


use

{benefit.tax_year} = cstr{?enddate},"yyyy")

 

[silck]

Thanks for the suggestion, but that still does not resolve it.

 

[lupins46]

Given that you say a formula which should produce 204 is giving a value of 2004 and a direct extract of the year does not match, then I think there is something about the data that you are not telling us.

In particular what is the value of ?enddate, if you print it in the report?

 

[silck]

I have changed the select criteria to

{benefit.tax_year} = cstr(({?enddate}),"yyyy")
or
isnull({BENEFIT.TAX_YEAR})

Using the {?enddate} of 9/30/2004, the report is still only pulling the 2004({BENEFIT.TAX_YEAR}). If I reverse it to:

isnull({BENEFIT.TAX_YEAR})
or
{benefit.tax_year} = cstr(({?enddate}),"yyyy")

It still only pulls the null values and I need to it to pull both.

All of your help is appreciated!

 

[kskid]

Crystal version, Database and connectivity would help.

I aggree with lupins46.

I would create a new report with just benefit table. Suppress the detail section and insert a group on {benefit.tax_year} and see what you actually get.

You might also check your Show SQL Query and see if the 2004 is passed.

 

[silck]

Not sure why it didn't before, but the following worked today:

isnull({BENEFIT.TAX_YEAR})
or
{benefit.tax_year} = cstr(({?enddate}),"yyyy")


Thanks for all your help!!!

 

[Madawc]

When writing formulas, note that any test for null must always come first. Without isnull, Crystal will stop processing the formula when it hits a null value; the default assumption being that null means show nothing. I made this mistake several times while I was learning Crystal, because it's not obvious to someone who's used to other programming languages.

In your case, it shouldn't make a difference because you are only testing the possibly-null field. But it is a good habit to get into.

If I were doing it, I'd also have defined the year from the end-date as a distinct formula field. Then you could place it on the report to see what was in it.

Madawc Williams (East Anglia, UK)