| Author |
Message |
pshilpain2000
Newbie
Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
|
  
Posted: 11 Mar 2009 at 12:58pm |
|
hey you know what ..... i just now saw that option .... Convert Nulls to Default in record selection editor. Let me try that. Thanks.
|
 IP Logged |
|
|
|
pshilpain2000
Newbie
Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 12 Mar 2009 at 4:46pm |
hello everyone,
on a special note, i would like to thank everyone's suggestions, i am trying al possible ways as suggested, but not getting accurate results [as i know the number of records that should be returned] ... will keep you all updated once i get any solution.
thanks,
sil. Edited by pshilpain2000 - 12 Mar 2009 at 4:46pm
|
| IP Logged |
|
pshilpain2000
Newbie
Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 23 Mar 2009 at 3:04pm |
Hello Everyone,
I apologize for being so late to find a solution for this problem. I was stuck in other emergency issues these days .....
Well, Jbleep Thanks a bunch ..... it solved my problem ..... i was trying to do something similar in the record selection formula editor. we have an option there too "Default Values for Nulls". but that didnt solve my problem ....
I used the same code as below along with what jbleep suggested to do ..... and I got my exact set of records .....
if {?Parameter1}=0
then ( (DBcolumn2 = {?Parameter2} OR {?Parameter2} = 'ALL') and ({DBcolumn3} = {?Parameter3} OR {?Parameter3} = 'ALL') and ({DBcolumn4}={?Parameter4} OR {?Parameter4}='ALL') and ({DBcolumn5}={?Parameter5} OR {?Parameter5}='ALL') and ({DBcolumn1}in [val1,val2,val3,val4,val5,val6,val7]) AND ({DBcolumn6}={?Parameter6} OR {?Parameter6}='ALL')
AND {?StartDate}<={DBcolumn7} and
({?EndDate}>={DBcolumn8} or isnull({DBcolumn8})) )
else
(
(DBcolumn2 = {?Parameter2} OR {?Parameter2} = 'ALL') and ({DBcolumn3} = {?Parameter3} OR {?Parameter3} = 'ALL') and ({DBcolumn4}={?Parameter4} OR {?Parameter4}='ALL') and ({DBcolumn5}={?Parameter5} OR {?Parameter5}='ALL') and ({DBcolumn1}= {?Parameter1}) and ({DBcolumn6}={?Parameter6} OR {?Parameter6}='ALL')
AND {?StartDate}<={DBcolumn7} and
({?EndDate}>={DBcolumn8} or isnull({DBcolumn8}))
)
Thanks once again ..... but now i get a new problem ..... for the null values for DBcolumn8, I want to display "No Date" .. I created a formula something like this ...
IF ISNULL({DBcolumn8}) Then
"No Date" else left(cstr({DBcolumn8}),10)
but still it is giving me blanks where there is a null value for DBcolumn8, instead of giving me 'No Date'
please suggest ... Thanks to Everyone once again .....
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Mar 2009 at 7:24am |
YOu can try to see if any of those "blank" values are meeting anouther criteria other than a NULL
IF ISNULL({DBcolumn8}) or {DBcolumn8}="" or {DBcolumn8}=(#01/01/1900#) Then
"No Date" else left(cstr({DBcolumn8}),10)
|
| IP Logged |
|
pshilpain2000
Newbie
Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 24 Mar 2009 at 9:47am |
you know it worked ... thanks a lot ....  .... but i have a question though .... which i ought to understand ....
what does this imply ..... sorry, but i didnt understand this part .....
{DBcolumn8}=(#01/01/1900#) Edited by pshilpain2000 - 24 Mar 2009 at 9:51am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Mar 2009 at 9:49am |
|
unless i am mistaken that is the default date for nulls (at least for SQL)
|
| IP Logged |
|
pshilpain2000
Newbie
Joined: 10 Mar 2009
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 24 Mar 2009 at 9:56am |
Thanks a lot to DBlank and jbleep and everyone to share their views and help me solve my problem.
This forum is an awesome place. 
Finally my formula goes as follows:
if isnull({DBColumn8}) or cstr({DBColumn8})= "" or {DBColumn8}=(#01/01/1900#) then "No Date" else left(cstr({DBColumn8}), 10)
and record selection sql as i have mentioned in my before post ....
Heartful Thanks once again for solving my problem. I published the report and the user is happy with it.
|
| IP Logged |
|
TLH1
Newbie
Joined: 15 Apr 2009
Online Status: Offline
Posts: 2
|
Posted: 15 Apr 2009 at 3:05pm |
Hello - I have a similar problem and have also not quite been able to resolve it. I have record selection with several parameters and for one field "CHARLIE", if the wildcard "ALL" is selected by the user, none of the null values in that field are returned.
I have tried a couple of the suggestions above, but they have not worked either. Below is the last code I've tried (which Crystal will not accept), any further suggestions? (I always have a feeling it's down to a bracket!)
(if {?ALPHA} ="ALL" then {ALPHA} like "*" else {ALPHA} ={?ALPHA}) and
(if {?BRAVO} = "ALL" then {BRAVO} like "*" else {BRAVO} = {?BRAVO}) and
(if isnull({CHARLIE}) then " " else if{?CHARLIE} ="ALL" then {CHARLIE} like "*" else {CHARLIE} = {?CHARLIE}) and
(if {?DELTA} ="ALL" then {DELTA} like "*" else {DELTA} = {?DELTA}) Edited by TLH1 - 15 Apr 2009 at 3:09pm
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Apr 2009 at 3:12pm |
Not sure but try the following...I bolded and red inked the changes (you also may need left joins for these NULLS to get pulled in depending on if you are using "Charlie" as table join...
(if {?ALPHA} ="ALL" then {ALPHA} like "*" else {ALPHA} ={?ALPHA})
and
(if {?BRAVO} = "ALL" then {BRAVO} like "*" else {BRAVO} = {?BRAVO}) and
(if{?CHARLIE} ="ALL" then (isnull({CHARLIE}) or {CHARLIE} like "*") else {CHARLIE} = {?CHARLIE})
and
(if {?DELTA} ="ALL" then {DELTA} like "*" else {DELTA} = {?DELTA})
|
| IP Logged |
|
TLH1
Newbie
Joined: 15 Apr 2009
Online Status: Offline
Posts: 2
|
Posted: 15 Apr 2009 at 3:19pm |
Yes! That worked! Thank you for your speedy response!
|
| IP Logged |
|
|
|
