AND/OR formula

[leanneconnexions]

This is returning a null dataset. I know it should return one at least as I have a record that contains FD and F1. What is wrong with the formula? In my dataset I need all history categories D and where there is also 0 or 1 or 2 etc.
HELP! ;-)

{CR_HISTORY.HistoryType_Ref} = 'F' and
{CR_HISTORY.HistoryCategory_Ref} = 'D' and
{CR_HISTORY.HistoryCategory_Ref} = '0 or 1 or 2 or 3'

 

[lupins46]

{CR_HISTORY.HistoryCategory_Ref} = '0 or 1 or 2 or 3'

This is testing for a text string equal to the everything between the quotes.

Assuming this is a Selection formula then you can change it to:
{CR_HISTORY.HistoryCategory_Ref} in [0,1,2,3]

 

[MisterMo]

if the field is alphanumeric then
{CR_HISTORY.HistoryType_Ref} = 'F'
and
{CR_HISTORY.HistoryCategory_Ref} in ['D', '0','1','2','3']


Mo

 

[lupins46]

Two tests on the same field - I missed that -
well spotted Mo

 

[leanneconnexions]

Thanks. I have tried

{CR_HISTORY.HistoryType_Ref} = 'F'
and
{CR_HISTORY.HistoryCategory_Ref} in ['D', '0','1','2','3'] and I still only get the D records returned. I do not get the record that has D and 0.

 

[MisterMo]

could you give a sample of your data from both fields

Mo

 

[leanneconnexions]

HistoryCategory_Ref is coded as 0-9 and A-Z. I want to be able to show for all those client records where history insert 'D' is record. where any other HistoryCaregory is recorded also. The HistoryCategory field is a string.

 

[MisterMo]

How long is the string 1 char? 2,3 ?

what I need is a sample data e.g.

field1 field2
D BLABLA
0 NNNNN
1 IIIII
D UUUUUU



Mo

 

[leanneconnexions]

Both HistoryType and HitsoryCategory are Char 1.

 

[lupins46]

So how can HistoryCategory_Ref be BOTH "D" AND "1"?

 

[MisterMo]

based on what you wrote

I still only get the D records returned. I do not get the record that has D and 0.

my understanding was that there were more than on char on your field. (D and 0)

as I asked before, could you please give a sample of the data.





Mo

 

[leanneconnexions]

yes i want the report to show me where history category matched D for definate and any others ie 0-9A-Z.

 

[GJParker]

Please give a sample of your data rather than continuosly trying to describe it, it would be far easier to see your problem.



Gary Parker
MIS Data Analyst
Manchester, England

 

[leanneconnexions]

Client_Ref HistoryType HistoryCategory DateStart
ZIMAS00 F 6 8-Jul-04
ZIMAS00 F 5 8-Jul-04
ZIMAN00 F 8 25-Feb-05
ZIMAN00 F 7 27-Jan-05
ZIMAN00 F 7 13-Dec-04
ZIMAN00 F 7 19-Oct-04
ZIMAN00 F 7 1-Oct-04
ZIMAN00 F 7 7-Oct-04
ZAKIN00 F S 23-Apr-04
YU__C00 F H 3-Aug-04
YOUNT05 F 8 9-Dec-02
YOUNN01 F 0 23-Jan-03
YOUNK03 F S 1-Jul-02
YOUNJ19 F J 25-Nov-02
YOUNJ19 F U 25-Nov-02
YOUNJ19 F W 25-Nov-02
YOU_L00 F B 1-Sep-01
YORKT00 F B 1-Sep-01
YORKS00 F B 1-Sep-01
YIP_H00 F B 3-Feb-03
AINDJ00 F D 14-Aug-05
AINDJ00 F 0 15-Aug-05

 

[kskid]

Do you happen to have a group selection formula on date? That could account for not seeing subsequent records. If not, show your record selection formula so we can see what is happening.

-lw

 

[leanneconnexions]

no there is no group selection in the report.

I used to select all the F Type Histories:

{CR_HISTORY.HistoryType_Ref} = "F" and
{CR_HISTORY.HistoryCategory_Ref} = "D"

I then tried the 2 selections below to get the report to show those records where D and another is recorded:

{CR_HISTORY.HistoryType_Ref} = 'F' and
{CR_HISTORY.HistoryCategory_Ref} = "D" and
{CR_HISTORY.HistoryCategory_Ref} In ['0' or '1'or '2'or '3']


{CR_HISTORY.HistoryType_Ref} = 'F' and
{CR_HISTORY.HistoryCategory_Ref} = "D" and
{CR_HISTORY.HistoryCategory_Ref} In ['0','1','2','3']

 

[MisterMo]

it seem to me that there might be excess data on your fields

try

{CR_HISTORY.HistoryType_Ref} = 'F'
and
trim({CR_HISTORY.HistoryCategory_Ref}) in ['D', '0','1','2','3']


Mo

 

[GJParker]

using your sample then the formula provided above should work

{CR_HISTORY.HistoryType_Ref} = 'F' and
{CR_HISTORY.HistoryCategory_Ref} in ['D','0','1','2','3']

or the long winded way is

{CR_HISTORY.HistoryType_Ref} = 'F' and
({CR_HISTORY.HistoryCategory_Ref} = 'D' or
{CR_HISTORY.HistoryCategory_Ref} = '0' or
{CR_HISTORY.HistoryCategory_Ref} = '1' or
{CR_HISTORY.HistoryCategory_Ref} = '2' or
{CR_HISTORY.HistoryCategory_Ref} = '3')

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[leanneconnexions]

Many Thanks for all your help. It hasnt given me exactly what I want but I can use the data from the result set. ;-)

 

[lupins46]

Again, assuming this is a record selection formula, the answer you were given before :

{CR_HISTORY.HistoryType_Ref} = 'F' and
{CR_HISTORY.HistoryCategory_Ref} in ['D', '0','1','2','3']

should select the last 2 rows from your sample list.

But I suspect that what you are really asking is more complicated.
Those of us trying to answer your question would regard each line of your sample data as a record. I am now thinking that is not what you mean.
I suspect you are using the term 'record' to mean 'all rows for the same client-ref' - is that correct?
So do you mean you want to see Client-ref records only if Type = 'F' and there is at least one record with Category 'D' and at least one record with category 0-5 for that client?