Crystal Reports Record Selection with prompts ignores second or statement

[src6878]

Problem is If I select criteria for the second or statement ({HiredRate.UTRANSDOC}startswith{?TransYN}) and no for the first ({HiredRate.UTRANSWEB}startswith{?WebYN}) I get only one record. If I switch places in the formula putting ({HiredRate.UTRANSDOC}startswith{?TransYN}) 1st I get all of the data.

When I run the SQL query I get all of the data no matter what order they are in but The Crystal Preview only gives me all of the data on the first from the OR section.

Here is the Crystal Record Selection Formula

{HiredRate.CONTSUPREF} startswith {?LanguageCombo}
and
{HiredRate.ONDATE} = {?ProjectDate}
and
{HiredRate.ACTVCODE}= "SIG"
and
{HiredRate.RESULTCODE} = "CLM"
and

({HiredRate.UTRANSWEB}startswith{?WebYN}
or
{HiredRate.UTRANSDOC}startswith{?TransYN}
or
{HiredRate.UTRANLANL0}startswith{?LanloYN}
or
{HiredRate.UINTCONSEC}startswith{?InterpYN}
or
{HiredRate.UINTCONF}startswith{?IntConfYN}
or
{HiredRate.UINTOPI}startswith{?OPIYN})


Here is the SQL query Crystal is using:


SELECT HiredRate.DEAR, HiredRate.CONTSUPREF, HiredRate.LASTDATE, HiredRate.CONTACT, HiredRate.USOURCLANG, HiredRate.UTARGLANG, HiredRate.UTRANSDOC, HiredRate.UTRANSWEB, HiredRate.UTRANLANL0, HiredRate.UINTCONSEC, HiredRate.UINTCONF, HiredRate.UINTOPI, HiredRate.ONDATE, HiredRate.ACTVCODE, HiredRate.RESULTCODE
FROM GoldMine_Main.dbo.HiredRate HiredRate
WHERE HiredRate.CONTSUPREF LIKE 'ENG>SPA%' AND (HiredRate.ONDATE>={ts '2012-04-01 00:00:00'} AND HiredRate.ONDATE<{ts '2013-04-06 00:00:00'}) AND HiredRate.ACTVCODE='SIG' AND HiredRate.RESULTCODE='CLM' AND (HiredRate.UTRANSWEB LIKE 'NO%' OR HiredRate.UTRANSDOC LIKE 'YES%' OR HiredRate.UTRANLANL0 LIKE 'NO%' OR HiredRate.UINTCONSEC LIKE 'NO%' OR HiredRate.UINTCONF LIKE 'NO%' OR HiredRate.UINTOPI LIKE 'NO%')
ORDER BY HiredRate.DEAR, HiredRate.CONTACT

 

[src6878]

Update to original post:

The only thing that stands out looking at the data from SQL is that the one record Crystal is returning has YES in the Transdoc field and the Transweb field is blank. All other records show YES for Transdoc and NULL for the Transweb field.

 

[src6878]

I found a solution by another Tech, apparently issue with NULL values in Crystal

New Formula:

{Product.Size} <> “xsm” or IsNull({Product.Size})

Unfortunately, when preview your report, you will find that this doesn’t work. That is not because of a mistake in our logic but rather, what I consider to be a bug in Crystal Reports. If I took this exact same condition and applied it to the database records using a query analyzer or querying tool, I would see the blank records. Unfortunately, Crystal is not allowing the null values to come through even though our formula says that they should.

The trick to circumventing this bug is to put the IsNull() check FIRST in the formula.

Thus, if we rearrange the condition to this:

IsNull({Product.Size}) or {Product.Size} <> “xsm”

 

[kray4660]

Put the isNull first and see it that makes a difference. A Null can 'kill' a crystal formula if you do not check for it first (IsNull({Product.Size}) or {Product.Size} <> “xsm” ).