Conditioning for null value not working

[tcbc]

I have the below formula in a report and I am trying to condition for field ({master_cred.first_name}) being null if it is null. If this field is null, the formula below will not work. Rather report brings back nothing. The report does have approx 5 parameters. One of the parameters is for ({master_cred.first_name}). I want records to come up even if this field is null and a different parameter was used.

(If IsNull({?PRR }) or {?PRR } = " " then
{group_detail.lang1} like "*" else
{group_detail.lang1} = {?PRR }) and
(If IsNull({?City}) or {?City} = " " then
{group_detail.city} like "*" else
{group_detail.city} = {?City}) and
(If IsNull({?County}) or {?County} = " " then
{group_detail.county} like "*" else
{group_detail.county} = {?County}) and
(If IsNull({?Key#}) or {?Key#} = " " then
{group_detail.lookup_key} like "*" else
{group_detail.lookup_key} = {?Key#}) and
{group_detail.archive_status} = "Y" and
(If IsNull({?Site#}) or {?Site#} = " " or isnull({master_cred.first_name})then
{master_cred.first_name} like "*" or isnull({master_cred.first_name})else
{master_cred.first_name} = {?Site#}) and
{master_cred.active} = "o" and
(If IsNull({?Office Name}) or {?Office Name} = " " then
{group_detail.group_desc} like "*" else
{group_detail.group_desc} like "*"&{?Office Name}&"*") and
{master_cred.med_staff_status} = "SCP" and
{master_cred.status} = "ACTIVE" and
{group_detail_1.location} = "CONTRACT" and
{group_detail.location} = "site

 

[Turkbear]

Hi,
Reorganizing like this for clarity may show the issue:

(
 If (IsNull({?PRR })  or {?PRR } = " ")
  then
   {group_detail.lang1} like "*" 
  else
    {group_detail.lang1} = {?PRR }
) 
and
(
 If (IsNull({?City})  or {?City} = " ")
  then
   {group_detail.city} like "*" 
  else
   {group_detail.city} = {?City}
) 
and
(
 If (IsNull({?County})  or {?County} = " ")
  then
  {group_detail.county} like "*" 
  else
  {group_detail.county} = {?County}
) 
and
(
 If (IsNull({?Key#}) or {?Key#} = " ")
  then
  {group_detail.lookup_key} like "*" 
  else
  {group_detail.lookup_key} = {?Key#}
) 
and
{group_detail.archive_status} = "Y" 
and
[COLOR=red]//problems here - rework
(
 (If IsNull({?Site#}) or {?Site#} = " " or isnull({master_cred.first_name}))
 then
 ({master_cred.first_name} like "*" or isnull({master_cred.first_name}))
 else
 ({master_cred.first_name} = {?Site#}) and
{master_cred.active} = "o" )
)
and
(
 If (IsNull({?Office Name})  or {?Office Name} = " " )
 then
 {group_detail.group_desc} like "*" 
 else
 {group_detail.group_desc} like "*"&{?Office Name}&"*"
) 
[/color]
and
{master_cred.med_staff_status} = "SCP" and
{master_cred.status} = "ACTIVE" and
{group_detail_1.location} = "CONTRACT" and
{group_detail.location} = "site"

The red section is very difficult to follow and seems to have some contradictions - for instance, if {master_cred.first_name} Is Null it Cannot be like "*".

I do not know your data well enough to rework it, but try reorganizing that part ( and maybe place it before the and {group_detail.archive_status} = "Y" line since all Nulls should be tested for first before other conditions .






To Paraphrase:"The Help you get is proportional to the Help you give.."