"OR" not returning expected fields

[worsleyl]

CR 9.0/Sybase ASA 6.0.2
Table Activity LOJ to table Program.ProgDesc (to determine program name that may or may not have been selected for Activity)
Table Activity also LOJ to table PatReg.ProgNo (through 2 other tables) (to determine program name patient belongs to if activity included patient)

I need to pull all activities relating to Acquired Brain Injury. I can tell that because:
the Program.ProgDesc = 'Acquired Brain Injury'
or
the PatReg.ProgNo = 83

For some activities both fields will be entered
For some only one or the other of progdesc or progno will be entered

Example

Activity No ProgDesc ProgNo
1 Acquired Brain Injury 83
2 Acquired Brain Injury
3 83
4 Developmental Clinic 111
5 Developmental Clinic
6 111

The report needs to return activity no 1,2 and 3

{Activity.ActivityDateTime} = {?Activity Date/Time Range} and
({Prog.ProgDesc}= "Acquired Brain Injury" OR {PatReg.ProgNo}= 83)
//does NOT return activity 3
//returns activity 1 and 2


{Activity.ActivityDateTime} = {?Activity Date/Time Range} and
({PatReg.ProgNo}= 83 OR {Prog.ProgDesc}= "Acquired Brain Injury" )
//does NOT return activity 2
//returns activity 1 and 3

I've been trying all sorts of combos of the above but no go. Any help much appreciated. Please let me know if I haven't provided enough info....

 

[GMcNamara]

You need to account for null values. Try this:

{Activity.ActivityDateTime} = {?Activity Date/Time Range} and
((not isNull({Prog.ProgDesc}) and
{Prog.ProgDesc} = "Acquired Brain Injury&quot or
(not isNull({PatReg.ProgNo}) and
{PatReg.ProgNo}= 83))

 

[worsleyl]

Perfect! I am an inexperienced user - had known about being aware of null values and had tried accounting for them but was using IsNull and getting way too many records back.

Deep gratitude! Hours of exasperation solved in 1 minute! Enjoy your weekend...

 

[Madawc]

Note that you have to test a field for null before testing it for anything else. Otherwise it will stop for a null, never mind that you had allowed for it later on in the formula. This feature of Crystal tripped me up when I first started on it, having come from a mainframe background.

Madawc Williams
East Anglia, Great Britain

 

[worsleyl]

Thanks for the heads up Madawc - I take that to mean that I include the not isNull ({field}) before the and statement?

I am still struggling with this concept. I am working on another report with the same premise, only this time I am excluding activities if they have a certain progdesc or a certain progno. And it turns out that some records will have neither field and they must be included. For example

Activity No ProgDesc ProgNo
1 Acquired Brain Injury 83
2 Acquired Brain Injury
3 83
4 Developmental Clinic 111
5 Developmental Clinic
6 111
7

I need to have activityno 4,5,6 AND 7 display
I can get 4,5,6 to display but NOT 7 with this selection formula:

not ({ActivityType.ActivityTypeDesc} like "Cancelled*&quot
and
{Activity.ActivityDateTime} >= {?Start Date} and
{Activity.ActivityDateTime}<= {?End Date}
and
not ({Person.LastName} in [&quot;Johnston&quot;, &quot;Graves&quot;])
and
not ({ServCat.ServCatDesc} in [&quot;Infant Development&quot;, &quot;Medical Services&quot;, &quot;Administration&quot;])

and
(not isNull ({Prog.ProgDesc}) and
(not IsNull ({PatReg.ProgNo}))
or
(not isNull({Prog.ProgDesc}) and
not ({Prog.ProgDesc} in [&quot;Acquired Brain Injury&quot;, &quot;Early Expressions&quot;, &quot;Genetics&quot;, &quot;Infant Development Program&quot;, &quot;Medical Services&quot;, &quot;Psychiatry&quot;]) and
not ({Prog.ProgDesc} like &quot;CCAC*&quot
or
(not isNull({PatReg.ProgNo}) and
not ({PatReg.ProgNo} in [107, 108, 119, 120, 122, 135, 136, 137, 138, 139, 140, 83, 98])
)


and
not ({WorkloadType.WorkloadTypeDesc} like [&quot;Non-worked*&quot;, &quot;Not At Work&quot;])))

Man, it looks messy! Any suggestions? I'll get my head around this yet!

 

[worsleyl]

Hi - think I've got it figured out - I was using not isNull instead of isNull ....

........
( isNull ({Prog.ProgDesc}) and
( IsNull ({PatReg.ProgNo}))or
(not isNull({Prog.ProgDesc}) and
not ({Prog.ProgDesc} in [&quot;Acquired Brain Injury&quot;, &quot;Early Expressions&quot;, &quot;Genetics&quot;, &quot;Infant Development Program&quot;, &quot;Medical Services&quot;, &quot;Psychiatry&quot;]) and
not ({Prog.ProgDesc} like &quot;CCAC*&quot or
(not isNull({PatReg.ProgNo}) and
not ({PatReg.ProgNo} in [107, 108, 119, 120, 122, 135, 136, 137, 138, 139, 140, 83, 98])
)))