Author |
Message |
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
Topic: Selection Criteria Posted: 29 Nov 2013 at 5:55am |
I am a Crystal Report Novice, so please be gentle with me. I am trying to select records where Field 1 does not contain "HHGD" or is blank, AND Field 2 is not equal to "Storage". BOTH conditions must be true. If the record has "HHGD" in Field 1 and something other than "Storage" in Field 2, I want it in the report. There is also other criteria, but I can figure out how to add that. I'm using Version 14. Thanks for your help. Jayn
|
IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 30 Nov 2013 at 6:43pm |
Hi Try this in record selection Formula : {field1} = "HHGD" and {Field2} <> "Storage" Also on top of your formula editor window, you will find 'Exceptions for Nulls' make that as 'Default Values for Nulls'
|
Thanks,
Sastry
|
IP Logged |
|
lolly54
Groupie
Joined: 25 Sep 2011
Online Status: Offline
Posts: 58
|
Posted: 09 Dec 2013 at 9:49pm |
Or try...
({Field1} like "*HHGD*" or not(ISNULL{Field1})) AND not({Field2} = "Storage")
sometimes for excluding null value, I used the following also. {Field1} <> ""
Try them out and see. :)
|
IP Logged |
|
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
Posted: 10 Dec 2013 at 2:58am |
Hello Sastry and lolly54,
Sastry, when I try your suggestion, I don't get any records where Field 1 is not HHGD or blank.
lolly54
When I try yours Crystal is asking for a ) but I can't figure out where to put it.
I am trying to select records where Field 1 does not contain "HHGD" or is blank, AND Field 2 is not equal to "Storage". BOTH conditions must be true. If the record has "HHGD" in Field 1 and something other than "Storage" in Field 2, I want it in the report
|
IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 10 Dec 2013 at 9:15pm |
Hi Try this : (Not({Field1} like "*HHGD*") OR not(isnull({Field1})) or {field1} <>" ") and not({Field2} = "Storage")
|
Thanks,
Sastry
|
IP Logged |
|
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
Posted: 11 Dec 2013 at 3:11am |
Hi Sastry,
Thanks for your help. I tried that formula. The problem is that I have a record where Field 1 = Not HHGD, Field 2 = Storage. I need that record to show on this report. When I apply the formula, that record is not selected.
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 11 Dec 2013 at 6:42am |
(Not({Field1} like "*HHGD*") OR not(isnull({Field1})) or {field1} <>" " or {field1} <> "Not HHGD") and
not({Field2} = "Storage")
should work
|
IP Logged |
|
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
Posted: 11 Dec 2013 at 8:59am |
I'm sorry I'm not being clear. Select Records: If Field 1 is not equal to "HHGD" or if Field 1 is blank, AND Field 2 is not equal to "Storage". BOTH conditions must be true. If the record has "HHGD" in Field 1 and something other than "Storage" in Field 2, I want it in the report.
I got the isnull part. The issue is that I am trying to put two conditions together into one condition. BOTH PARTS of the condition must be true. When I put () around to try to say that, Crystal takes the () away and makes them like two separate conditions that are not dependent on each other.
Edited by jswartz - 11 Dec 2013 at 9:00am
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 11 Dec 2013 at 9:18am |
that's just a more complex...
local stringvar x := {table.field1};
if isnull(x) then x := ""; //deal with the null value
x:= trim(x); //remove leading and trailing white space
if x = "HHGD" or x = "" then
if {table.field2} <> "Storage" then
true
else
false
else
false
this should get you what you are after, and I have been wrong before. I was just building on what Sastry had written.
HTH
|
IP Logged |
|
jswartz
Newbie
Joined: 24 May 2013
Location: United States
Online Status: Offline
Posts: 11
|
Posted: 11 Dec 2013 at 9:37am |
Yeah that is a little more complex :-)
OK I copied and pasted this into the Formula Workshop for Record Selection. Replaced the {field} with my fields. Crystal says "A field is required here" at
if isnull(x)
|
IP Logged |
|
|