Select criteria problems 
Author Message
 Select criteria problems

Hi All,

I've been finding as my select criteria get more complex Crystal is having
trouble. For example, if I want to select clients who have not paid and use
a select statement "IsNull(Date_Paid)", and I also have several other select
criteria, Crystal sometimes returns an empty set - but if I make this the
first select statement Crystal seems to do OK. Now if I add a couple of
other complex select statements; for example the client list is large and
sometimes the report is run for individuals but sometimes the report is run
for all clients, I add "ALL" to the parameter default list and create a
select criteria statement "If  ?client = "ALL" then client_name <> "" else
client_name =  ?client". Now I might only want to look at clients still
alive so I add IsNull(Date_of_Death), and I only want to report on clients
of a specific age group so I add parameters for birthdate. My final select
criteria statement might be:

IsNull(Date_Paid) AND
If  ?client = "ALL" then client_name <> "" else client_name =  ?client AND
IsNull(Date_of_Death) AND
Date_of_Birth  IN  {?DateRangeStart} TO {?DateRangeEnd}

I've had Crystal fall on it's face with this type of select criteria. Often
by re-arranging the order of the statements I can get the report to produce
data but I never feel confident it will work all the time.
Has anyone else experienced this problem, and is there a way I can make a
report of this type more reliable?
Thanks in advance for any info,
Bruce



Sat, 21 Feb 2004 22:22:57 GMT  
 Select criteria problems
Hi Bruce,

If I tried to put an If ..Then .. Else in the middle of a selection formula I
would expect CR to fall flat on its face.  Deal with these separately.  For
example, you could use the following in Selection Formula:

BooleanVar SelectClient;
If ?client = "ALL" Then
      SelectClient := True
Else
      SelectClient := (client_name =  ?client);

IsNull(Date_Paid) AND
SelectClient AND
IsNull(Date_of_Death) AND
Date_of_Birth  IN  {?DateRangeStart} TO {?DateRangeEnd}

Regards,

John................

Quote:

> Hi All,

> I've been finding as my select criteria get more complex Crystal is having
> trouble. For example, if I want to select clients who have not paid and use
> a select statement "IsNull(Date_Paid)", and I also have several other select
> criteria, Crystal sometimes returns an empty set - but if I make this the
> first select statement Crystal seems to do OK. Now if I add a couple of
> other complex select statements; for example the client list is large and
> sometimes the report is run for individuals but sometimes the report is run
> for all clients, I add "ALL" to the parameter default list and create a
> select criteria statement "If  ?client = "ALL" then client_name <> "" else
> client_name =  ?client". Now I might only want to look at clients still
> alive so I add IsNull(Date_of_Death), and I only want to report on clients
> of a specific age group so I add parameters for birthdate. My final select
> criteria statement might be:

> IsNull(Date_Paid) AND
> If  ?client = "ALL" then client_name <> "" else client_name =  ?client AND
> IsNull(Date_of_Death) AND
> Date_of_Birth  IN  {?DateRangeStart} TO {?DateRangeEnd}

> I've had Crystal fall on it's face with this type of select criteria. Often
> by re-arranging the order of the statements I can get the report to produce
> data but I never feel confident it will work all the time.
> Has anyone else experienced this problem, and is there a way I can make a
> report of this type more reliable?
> Thanks in advance for any info,
> Bruce



Sun, 22 Feb 2004 21:39:10 GMT  
 Select criteria problems
Also if you are checking for a field that might be null, the null test must be
before any onther test for that same field.

If {table.field} < 3 or isnull({table.field} . . . will not work, it wonlt give
you an error message, you just won't get the results you expect.

if isnull(table.field}) or {table.field} < 3. . . will work correctly

(I know this is true with version 7, may not be for later versions)



Wed, 03 Mar 2004 01:51:18 GMT  
 Select criteria problems
Yes, I thought I had noticed that was the case, thanks!

Quote:
> Also if you are checking for a field that might be null, the null test
must be
> before any onther test for that same field.

> If {table.field} < 3 or isnull({table.field} . . . will not work, it wonlt
give
> you an error message, you just won't get the results you expect.

> if isnull(table.field}) or {table.field} < 3. . . will work correctly

> (I know this is true with version 7, may not be for later versions)



Sat, 06 Mar 2004 21:31:06 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Select Case with multiple string criteria

2. Pass field value to criteria of select query.

3. Criteria for select case statements

4. Custom Dialogue Control to select criteria for reports

5. Fill Combo List using Criteria selected in another Combo List

6. (CRY)stal Pro Select Criteria - Date

7. Fill Combo List using Criteria selected in another Combo List

8. AppointmentItem.Items.Find (criteria) - how to specify Start and End criteria

9. Problem SELECT DISTINTC / Problema SELECT DISTINTC indeseado

10. Problem with SQL Criteria

11. Problems using CommandBarComboBox as search criteria for FindRecord method

12. strange "criteria" problem

 

 
Powered by phpBB® Forum Software