Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Parameter query returning Null values Post Reply Post New Topic
Author Message
stanley721
Newbie
Newbie
Avatar

Joined: 12 Jul 2011
Location: United States
Online Status: Offline
Posts: 8
Quote stanley721 Replybullet Topic: Parameter query returning Null values
     Posted: 12 Apr 2013 at 7:53am
Using Crystal Reports 2008, I have a database I am trying to get info out of. I want the user to input a date range, and then in thAt date range, I want them to be able to enter search criteria from 3 different fields, but not have to enter a value in all 3. They can do 2 or just 1. But the problem is if they do not enter a value in any of the search fields, I do not want it looking for blanks in the data. I want it to just skip that search and move on to the next. I have used the following:
({RecordList.EntryDate} >= {?FromDate} and
{RecordList.EntryDate} <= {?ToDate} and
{RecordList.DocumentName} like {?SearchDocumentName}) and
({RecordList.EntryDate} >= {?FromDate} and
{RecordList.EntryDate} <= {?ToDate} and
{RecordList.RecordFiledFor} like {?SearchFiledFor}) and
({RecordList.EntryDate} >= {?FromDate} and
{RecordList.EntryDate} <= {?ToDate} and
{RecordList.Logged-in By} like {?Entered by})
 
And I've used this:
({RecordList.EntryDate} >= {?FromDate} and
{RecordList.EntryDate} <= {?ToDate} and
({RecordList.DocumentName} like {?SearchDocumentName} and
{RecordList.RecordFiledFor} like {?SearchFiledFor} and
{RecordList.Logged-in By} like {?Entered by})
 
But neither is working. What can I do to get what I am looking for?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Apr 2013 at 8:55am
(not hasvalue({?FromDate}) or {RecordList.EntryDate} in {?FromDate} to {?ToDate})
and
(not has value({?SearchDocumentName}) or {RecordList.DocumentName} like {?SearchDocumentName})
 and
( not has value({?SearchFiledFor}) or {RecordList.RecordFiledFor} like {?SearchFiledFor})
 and
(not has value({?Entered by}) or {RecordList.Logged-in By} like {?Entered by})
IP IP Logged
stanley721
Newbie
Newbie
Avatar

Joined: 12 Jul 2011
Location: United States
Online Status: Offline
Posts: 8
Quote stanley721 Replybullet Posted: 15 Apr 2013 at 1:10am
Tried that but it didn't work. It would not return any data. I messed around with it a bit and came up with :
({RecordList.EntryDate} >= {?FromDate} and {RecordList.EntryDate} <= {?ToDate}) and
 ((not hasvalue({?SearchDocumentName}) or {RecordList.DocumentName} like {?SearchDocumentName}) or 
(not hasvalue({?SearchFiledFor}) or {RecordList.RecordFiledFor} like {?SearchFiledFor}) or 
(not hasvalue({?Entered by}) or {RecordList.Logged-in By} like {?Entered by}))
 
But what that gives me is a combination of all three search criteria. I want it to return data from the dates entered; required. Then if I put in only one of the search criteria, it will return that and any of the other fields that are blank. If I put in 2 search criteria, then it has to meet both with the 3rd left blank, so on and so forth. The more searches entered, the more restrictive the search results. I guess what I have now will work, it's just that I want to be able to be more restrictive as if more criteria is entered. Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2013 at 3:50am
I was understanding your need to ignore the paramter but you want to actually search for null values if they leave the parameter blank, correct?
 
try
 
{RecordList.EntryDate} in {?FromDate} to {?ToDate}
and
((not hasvalue({?SearchDocumentName}) and isnull({RecordList.DocumentName})) or {RecordList.DocumentName} like {?SearchDocumentName})
 and
( (not has value({?SearchFiledFor}) and isnull({RecordList.RecordFiledFor})) or {RecordList.RecordFiledFor} like {?SearchFiledFor})
 and
((not has value({?Entered by}) and isnull({RecordList.Logged-in By})) or {RecordList.Logged-in By} like {?Entered by})
 
IP IP Logged
stanley721
Newbie
Newbie
Avatar

Joined: 12 Jul 2011
Location: United States
Online Status: Offline
Posts: 8
Quote stanley721 Replybullet Posted: 15 Apr 2013 at 4:11am
If the user leaves the search criteria blank, I want Crystal to ignore that search criteria as if it wasn't even there. I don't want it to search for blank entries, but I do want it to allow them if they meet the other criteria. Clear as mud, huh?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2013 at 4:24am
that is what I think the first post I gave you should be doing.
the not hasvalue() should evalaute to true when the param is left blank so it should ignore that param without using it to filter any data and then move to the next condition...
try adding in more parenths
 
{RecordList.EntryDate} in {?FromDate} to {?ToDate})
and
(not (hasvalue({?SearchDocumentName})) or {RecordList.DocumentName} like {?SearchDocumentName})
 and
( not (hasvalue({?SearchFiledFor})) or {RecordList.RecordFiledFor} like {?SearchFiledFor})
 and
(not (hasvalue({?Entered by})) or {RecordList.Logged-in By} like {?Entered by})
 
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum